Lookup through a range of numbers
 

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

Lookup through a range of numbers
 

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

Array entered** :

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

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

--
Biff
Microsoft Excel MVP


"Bob W" wrote in message
...
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

Lookup through a range of numbers
 

Slight tweak. Still array entered** :

=MAX((Sheet2!E1:E15=MIN(IF(Sheet2!E1:E15=A1,Sheet 2!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)

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
If there is no number = the lookup number this formula returns 0.

Array entered** :

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

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

--
Biff
Microsoft Excel MVP


"Bob W" wrote in message
...
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