LOOKUP and higher value

J

jonrtait

I'm currently using the LOOKUP function to return a corresponding value,
however if the number being looked for falls between 2 numbers in the list,
the lower of the two numbers is returned.

Is it possible for the higher of the two to be returned?
 
K

Ken Hudson

Hi,
There isn't a way to do it with VLOOKUP.
VLOOKUP will return the closest match - not the lowest.
 
D

Dave Peterson

One way is to look (maybe twice):

=INDEX(B1:B10,
IF(ISNUMBER(MATCH(C1,A1:A10,0)),MATCH(C1,A1:A10,0),1+MATCH(C1,A1:A10,1)))

(all one cell)

I put my table in A1:B10, the value to lookup in C1.
 
J

jonrtait

Dave - perfect.

It's been bugging me all day and you've cracked it for me.

Thanks very much!
 
S

Seth

Dave,

Thanks




Dave Peterson said:
One way is to look (maybe twice):

=INDEX(B1:B10,
IF(ISNUMBER(MATCH(C1,A1:A10,0)),MATCH(C1,A1:A10,0),1+MATCH(C1,A1:A10,1)))

(all one cell)

I put my table in A1:B10, the value to lookup in C1.
Click to expand...
 
Top