Ask Your Question

Is there a way to use 'Is Greater Than' criteria in VLOOKUP?

asked 2017-05-11 23:59:37 +0200

3D4Play gravatar image

updated 2017-05-12 00:11:28 +0200

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!

edit retag flag offensive close merge delete


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.

Lupp gravatar imageLupp ( 2017-05-12 00:36:30 +0200 )edit

1 Answer

Sort by » oldest newest most voted

answered 2017-05-12 00:30:36 +0200

Lupp gravatar image

updated 2017-05-12 00:39:06 +0200

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) .

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2017-05-11 23:59:37 +0200

Seen: 14,413 times

Last updated: May 12 '17