using vlookup to find the next highest item in the list

P

pbertolas

I would like to know if there is a way to use the vlookup
or some other function to look up a value in a table
(that is not an exact value in the table) and return the
next highest entry in the adjacent column, not the next
lowest entry that is achieved with the parameter "False".

Perry
[email protected]
 
F

Frank Kabel

Hi
use an INDEX/MATCH combination with a descending sorted
list (and a -1 as third parameter of MATCH). e.g
=INDEX(B1:B100,MATCH(valöue,A1:A100,-1))
 
H

hgrove

Frank Kabel wrote...
use an INDEX/MATCH combination with a descending sorted
list (and a -1 as third parameter of MATCH). e.g
=INDEX(B1:B100,MATCH(value,A1:A100,-1))
Click to expand...
...

An alternative that doesn't require the OP to sort the list i
descending order on the first column,

=INDEX(B1:B100,MATCH(value,A1:A100)
+(LOOKUP(value,A1:A100)<>value)
 
Top