Menu
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:
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.
535
The “greater than or equal to” symbol (>=) is written in Excel by typing the “greater than” (>) sign followed by the “equal to” (=) operator. The operator “>=” is placed between two numbers or cell references to be compared. For example, type the formula as “=A1>=A2” in Excel.
VLOOKUP will only work if the lookup value is in the first column. VLOOKUP cannot look to its left. However, INDEX MATCH solves this problem as it performs the lookup both horizontally and vertically. So, it doesn't require the lookup value to be in the first column, it can be anywhere.
Use of SUMIFS with INDEX & MATCH Functions in ExcelIf you use the SUMIFS function with the INDEX and MATCH functions inside, you have the ability to add more than one criterion, which you can't do by just using the SUMIF function. To do this, ensure you input your Sum Range, then Criteria Range, then Range Criteria.
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
196
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
