Vlookup selecting next highest value instead of equal or nearest l

  • Thread starter Thread starter Jack
  • Start date Start date
J

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Yes or blank, depending if value equals or exceeds value found inVLookup table. 4
Dynamic col_index_num in VLOOKUP? 5
Value match with an array 1
use vlookup or other to find the nearest values (<>) or interpola 4
Formula have a VLOOKUP but want it to =0 if no value is found 3
RANK Function 6
Finding location (or start cell) of highest consecutive values 3
Trying to find additional ranges outside of min, max, and average 2

Back
Top