Look up table
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
-
Did you try setting the match type to False to specify an exact match?
Answers
-
Did you try setting the match type to False to specify an exact match?
-
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. 🙏
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!