Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
A lookup based on conditions
Hi Everyone,
I am curious if anyone knows a way to be able to look up a value based on whether or not a 'source' value falls in a range of possible values? In Short, if I have an ipnut (source) value of 100, I want to be able to do some form of lookup on a list, and return a result. The list may have a range of values such as 85-100, and a return value of 'apples'. If the input is 100, it is in the range of 85 to 110, and the value in the column next to the 85-100 range is 'apples'. If the input was 120 then the lookup would have to figure out which range of numbers the value falls into and then return the column next to that. I thought that it might be possible to have a column with the maximum number for a given range of values (in the example above it might be 110), then somehow compare the input value to see if it was less than the max value. Due to the number of possible sets of values that the input could possibly take there is no way to use a simple IF statement as you can only nest them so (7??) deep. I would love to be able to do this from a dynamic named range if possible. Anyone got any ideas? Cheers The Frog |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
A lookup based on conditions
It's possible, if the list is sorted in ascending order like
20 45 85 111 VLOOKUP(lookup_value,Table,2) so if the lookup value is 100 it will return what's next to 85 and if it is 112 it will return what's next to 111 -- Regards, Peo Sjoblom wrote in message ... Hi Everyone, I am curious if anyone knows a way to be able to look up a value based on whether or not a 'source' value falls in a range of possible values? In Short, if I have an ipnut (source) value of 100, I want to be able to do some form of lookup on a list, and return a result. The list may have a range of values such as 85-100, and a return value of 'apples'. If the input is 100, it is in the range of 85 to 110, and the value in the column next to the 85-100 range is 'apples'. If the input was 120 then the lookup would have to figure out which range of numbers the value falls into and then return the column next to that. I thought that it might be possible to have a column with the maximum number for a given range of values (in the example above it might be 110), then somehow compare the input value to see if it was less than the max value. Due to the number of possible sets of values that the input could possibly take there is no way to use a simple IF statement as you can only nest them so (7??) deep. I would love to be able to do this from a dynamic named range if possible. Anyone got any ideas? Cheers The Frog |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup in Table based on Two Conditions | Excel Discussion (Misc queries) | |||
shade cells based on conditions - i have more than 3 conditions | Excel Worksheet Functions | |||
Lookup Data based on 2 or more conditions/arguments | Excel Discussion (Misc queries) | |||
Counting based upon 2 conditions that are text based | Excel Discussion (Misc queries) | |||
Lookup based on two conditions | Excel Worksheet Functions |