Lookup function - can it return the higher value?

X

Xipha

I am trying to use the VLOOKUP function, except if the value is not found I
want the function to return the value one higher than the lookup value,
instead of rounding down to the nearest value. Is there a way to do that?
 
M

Mike H

Hi,

With your lookup table in A1 - B10 and the value to lookup in C1 try

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

Mike
 
Top