VLOOKUP Format

  • Thread starter Thread starter Critter
  • Start date Start date
C

Critter

The VLOOKUP function, by default, returns a value that is less than or
equal to the look-up value. Is there any way to change this so the
value returned is GREATER than or equal to the look-up value?
 
Take a look at MATCH, because you can specify whether you want the closest
number less than target, or closest number greater than target. But
according to the instructions you'll have to sort the lists to use it that
way.

You can combine MATCH with INDEX to return values from one column/row based
on lookup of another;

=INDEX(A1:A10,MATCH(300,B1:B10,-1))

The -1 is if you wanted the closest number greater than or equal to the
target.
A1:A10 is what you want to return, say Item names, based on weight which is
contained in B1:B10.
 
Suppose you have this:

=VLOOKUP(B1,A1:A5,1,TRUE)

To achieve what you require, sort A1:A5 descending and use this formula:

=INDEX(A1:A5,MATCH(B1,A1:A5,-1),1)

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel
 
Hi
have a look at MATCH (in combination with INDEX). Note: You have to
sort your range descending for this
 
Not by using vlookup, you can use this

=INDEX(C1:C10,MATCH(SMALL(B1:B10,COUNTIF(B1:B10,"<"&D1)+1),B1:B10,0))

where D1 is the lookup value, C1:C10 is the range where you want a value
returned from
and B1:B10 is the lookup range. You can name the table to MyTable and make
in generic

=INDEX(MyTable,MATCH(SMALL(INDEX(MyTable,,1),COUNTIF(INDEX(MyTable,,1),"<"&D
1)+1),INDEX(MyTable,,1),0),2)


Where the last 2 is the equivalent of vlookup's index column, a vlookup that
would return the less than or equal would look like

=VLOOKUP(D1,MyTable,2,1)
 
Back
Top