LOOKUP the number higher not lower

[Ivan]

I have a problem I'm trying to put together a spreadsheet and need the lookup
command, the lookup command is designed to look at the numbers in the table
and find the one that is the same or the next one down from what you are
looking for. What I need is to get it to look up the same number or the next
number up from the one Im looking for. Can someone help me on how to get that
done.

Thanks In Advance.

 

[dhstein]

How about copying the list in another column - but upside down. Then when
you search it will find the "lower" number. To copy the list upside down -
copy it into another column and then set a column to the left of the copied
column (for example column "K") with the numbers from 1 to whatever. Then
sort those 2 columns on column "K" descending. Maybe someone has a better
idea if that doesn't work for you.

 

[GSnyder]

Ivan,

If I'm reading this right, you want to return the exact match if it exists
and the next higher number if the exact match doesn't exist. To do that, we
can use the good old OFFSET function combined with a MATCH. The MATCH
statement will allow you to find the next higher number. In this formula,
we'll just check to see if the exact match exists. If it does, we'll use it.
If not, we'll use the OFFSET and MATCH to get the next higher number.

Assuming your list of numbers is sorted from low to high and is in A1:A100
and the number you want to find is in C2, then use:

=IF(ISERROR(VLOOKUP(C2,$A$1:$A$100,0)),OFFSET($A$1,MATCH(C2,$A$1:$A$100,1),0),VLOOKUP(C2,$A$1:$A$100,0))

 

[T. Valko]

Assuming the lookup value will not be greater than the max value in your
lookup table.

Try this array formula** :

A2:B15 = lookup table

D2 = lookup value

=INDEX(B2:B15,MATCH(MIN(IF(A2:A15>=D2,A2:A15)),A2:A15,0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.