LOOKUP and higher value

[Guest]

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?

 

[Guest]

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

 

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

 

[Guest]

Dave - perfect.

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

Thanks very much!

 

[Guest]

Dave,

Thanks

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.