Use INDEX MATCH to find greater than/equal to value
I've been searching online and in Excel Help but cannot find whether this is possible.
Given a list of numbers, values and a search term, I need to find the value whereby the value is greater than or equal to the search term. See the example:
Excel returns Red but it should return Orange because 15 is greater than 10.
Other desired result examples would be:
- 5 = Red (or N/A)
- 25 = Yellow
- 63 = Violet
- 15434 = Violet (or N/A)
I realise that if I sort the list in descending order and use -1 for the MATCH [match_type] argument, then it works okay, but, the list is over 1,000 rows and is far easier to read in ascending order.
Answer
Your desired results appear a little inconsistent. Why is the desired result for 15 Orange but that for 63 Indigo?
Are you in fact attempting to match based on whichever corresponding value is absolutely closest to the search value, whether below or above that value?
If so:
=INDEX(B4:B10,MATCH(TRUE,INDEX(ABS(A4:A10-B1)=MIN(INDEX(ABS(A4:A10-B1),,)),,),0))
though that still won't differentiate between e.g. Orange and Yellow for a search value of 25 (though I'm not sure how you are considering 25 "closer" to 30 than 20 in any case).
Regards