Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
lookup range of numbers; return corresponding data for the fixed r | Excel Worksheet Functions | |||
How to select top six numbers from a of range of random numbers | Excel Worksheet Functions | |||
two columns range of numbers need to list all numbers in the range | New Users to Excel | |||
Count comma separated numbers, numbers in a range with dash, not t | Excel Discussion (Misc queries) | |||
Lookup a range of numbers | Excel Discussion (Misc queries) |