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
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!