Another #NO MATCH problem
Some entries match, and some don't, making it complicated. So here we go. I am attaching screenshots.
Here is the reference "table"
Then, here is the main table:
Near Zero returns a "1" (good)
Very Low returned a "3" (good)
Low, Medium, and High all return "#NO MATCH" (bad)
Very High returns a "1" (Bad)
TIA - Tom
Best Answer
-
Range 1 is the numerical score and range 2 is the descriptive score, right?
The Very High issue is because the MATCH is not exact. It is finding the largest value less than or equal to "Very High" and thinks that is "Very Low".
Add a ,0 before the pink parenthesis.
For the NO MATCHes, can you share a screen shot of the main table with those in?
BTW - here are the options for that optional search_type argument in MATCH. I'd say you need to use 0 90% of the time.
For the optional search_type argument:
- 1: (The default value) Finds the largest value less than or equal to search_value (requires that the range be sorted in ascending order)
- 0: Finds the first exact match (the range may be unordered)
- -1: Finds the smallest value greater than or equal to search_value (requires that the range be sorted in descending order)
Answers
-
Range 1 is the numerical score and range 2 is the descriptive score, right?
The Very High issue is because the MATCH is not exact. It is finding the largest value less than or equal to "Very High" and thinks that is "Very Low".
Add a ,0 before the pink parenthesis.
For the NO MATCHes, can you share a screen shot of the main table with those in?
BTW - here are the options for that optional search_type argument in MATCH. I'd say you need to use 0 90% of the time.
For the optional search_type argument:
- 1: (The default value) Finds the largest value less than or equal to search_value (requires that the range be sorted in ascending order)
- 0: Finds the first exact match (the range may be unordered)
- -1: Finds the smallest value greater than or equal to search_value (requires that the range be sorted in descending order)
-
Thank you so much! The ",0" before the pink ) worked and its all coming in correctly. I would never have picked up on it.
-
Wonderful! Now you've fixed it, you'll be on the lookout for the little 0 in the future.
-
I sure will. - Best, Tom
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 351 Global Discussions
- 198 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 443 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 283 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!