Vlookup selecting next highest value instead of equal or nearest l

[Jack]

Hi All .
Is there any way we can make Vlookup select the next value that is highest
or equal to in a cell instead of the nearest lower value

 

[Arvi Laanemets]

Hi

You can make VLOOKUP select Exact value only setting 4th parameter to False.
With 4th paramaeter True (or omitted), your table must be sorted. I myself
never use VLOOKUP this way, but probably the sorting order (ascending vs.
descending) determines, is nearest higher or lower value returned, when
exact match doesn't exist.

 

[Ragdyer]

Assuming that the lookup values are sorted ascending,
revise this Vlookup() formula:

=VLOOKUP(D1,A2:B15,2)

with this formula:

=VLOOKUP(SMALL(A2:A15,COUNTIF(A2:A15,"<"&$D$1)+1),A2:B15,2)

 

[Charles Williams]

Hi Jack,

another alternative formula (longer but faster)

=IF(VLOOKUP(D1,A1:A20,1)=D1,INDEX($A$1:$B$20,MATCH(D1,A1:A20),2),INDEX($A$1:$B$20,MATCH(D1,A1:A20)+1,2))

Charles
__________________________________________________
The Excel Calculation Site
http://www.decisionmodels.com