Look up table

Options

Hello there,

Recently I encountered a very strange and persisting problem in SmartSheet which I couldn't find a solution for. I wonder if someone can help me here.

I created a lookup table which intends to grab a number, e.g. a hourly fee from a the table an use it in another location (another row, another column, even another sheet; I tested all). I listed different roles like Engineer, Director, worker, etc. each of them has fee. The formula works for all entries, except director. When the search value is "Director", the result is "#No match" but when the entry is any other value, like "Engineer", is gives me the correct result.

If I change the item to "MDirector" it is working, but if I change it to "DDirector" it is not. It means the word "Director" is not a problem, probably. I'm really confused. Does anyone have any opinion?

Best Answer

Answers

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭
    Answer ✓
    Options

    Did you try setting the match type to False to specify an exact match?


  • Mehran Bagherian
    Mehran Bagherian ✭✭✭✭✭
    Options

    Hi Mike,

    Amazing!! I just changed TRUE to FALSE, and it is working now. How come?

    Thank you. I spent hours on this subject this morning and tested many variant, but nothing worked.

    Really appreciated. 🙏

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭
    Options

    False looks up an EXACT match, True looks up a closest match and assumes the data is sorted. I assumed your roles are not sorted alphabetically. It probably couldn't find the match because the data isn't sorted and chose the closest match...

    MDirector probably worked because the director level in your list probably came after a letter before or equal to L in alphabetical order.

    This is the exact definition from the Formula function instructions under usage notes.

    • True (the default value) assumes that the range is sorted ascending and returns the nearest match that is less than or equal to ( <= ) search_value.
    • False returns the first exact match.


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!