Look up table

Mehran Bagherian

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?

Mike Wilday

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

https://help.smartsheet.com/function/vlookup

Mike Wilday

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

https://help.smartsheet.com/function/vlookup

Mehran Bagherian

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

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.

https://help.smartsheet.com/function/vlookup