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!