I am familiar with VLOOKUP in general, but am having trouble figuring out if/how to make the ‘searchcriterion’ value use ‘is greater than’. In other words, I don’t want to lookup a value equal to a cell value, I want to lookup a value that is greater than the ‘searchcriterion’ cell value. Intuitively I tried adding the “>” before the cell reference, but that does not work.
FYI, my lookup array values are in ascending order.
Is it possible to achieve this in some way?
Thank you!
Using 1 in the last parameter position of VLOOKUP() is assuring the ascending order. However you won’t get the first element GreaterThan, but the last element NOT GT (LessThanorEqual). That’s how the lookup is specified.
=VLOOKUP(13/7 ; $A$2:$C$2 ; 3 ; 1)`
works similar to the example in the answer below. It does not look for exact matches, however.
VLOOKUP() hasn’t a ‘Criterion’ parameter. If you can sort your data using the column where the matches are searched as the key, you can search for the last (botommost) occurence not greater than the value to compare to or for the last value not less than …
Searches by MATCH() are always done top down or left to right. A GreaterThan (GT) search will therefore only find the first row or column respectively meeting the condition. To do so is possible utilising array evaluation:
=MATCH(TRUE(); RangeToSearchIn > ValueToCompareWith; 0) will evaluate the middle parameter in array mode to an array of Boolean results …
The position you get back you then can use to index into a data range. Example:
=INDEX($C$2:$C$101 ; MATCH(TRUE();$A$2:$A$101 > 13/7 ; 0) ; 1) .