Next highest Value
 

I have a list of values...
B1 0
B2 238
B3 263
B4 510
B5 585
B6 774
B7 931

=INDEX(R22:R28,MATCH(R21,R21:R28,1)) works except when there is an exact
match (i.e., 238)

I would like a formula that will, when I enter a value in A1 that if not in
the list will look up the next highest value.

A1=238 Formula returns 238 A1=24 Formula returns 263

Help I cant see the forest for the trees....

Next highest Value
 

Try this array formula** :

=MIN(IF(B1:B7=A1,B1:B7))

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

If the lookup value is greater than the max value of the range the result
will be 0.

--
Biff
Microsoft Excel MVP


"Thanks" wrote in message
...
I have a list of values...
B1 0
B2 238
B3 263
B4 510
B5 585
B6 774
B7 931

=INDEX(R22:R28,MATCH(R21,R21:R28,1)) works except when there is an exact
match (i.e., 238)

I would like a formula that will, when I enter a value in A1 that if not
in
the list will look up the next highest value.

A1=238 Formula returns 238 A1=24 Formula returns 263

Help I cant see the forest for the trees....