Another #NO MATCH problem

DrTom

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

KPH

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)

KPH

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)

DrTom

Thank you so much! The ",0" before the pink ) worked and its all coming in correctly. I would never have picked up on it.

KPH

Wonderful! Now you've fixed it, you'll be on the lookout for the little 0 in the future.

DrTom

I sure will. - Best, Tom