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
Check out the Formula Handbook template!