Lookup through a range of numbers

[Bob W]

If I enter a number in a cell, lets say 1500. I need some sort of lookup to
find a number on another sheet that either is equal to, or larger than the
1500. What I need it to bring back exactly is, the first instance of either
1500, or the next largest number it finds. Although I have used Vlookup for
many things, I can't seem to make it work for this one.

Anyone have any ideas?????

Thanks

Bob

 

[T. Valko]

If there is no number >= the lookup number this formula returns 0.

Array entered** :

=SUM((Sheet2!E1:E15=MIN(IF(Sheet2!E1:E15>=A1,Sheet2!E1:E15)))*Sheet2!E1:E15)

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

 

[T. Valko]

Slight tweak. Still array entered** :

=MAX((Sheet2!E1:E15=MIN(IF(Sheet2!E1:E15>=A1,Sheet2!E1:E15)))*Sheet2!E1:E15)

The first formula I suggested would return an incorrect result if there is
more than one instance of the same number that is >= the lookup number.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)