A lookup based on conditions

[Mr.Frog.to.you]

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

 

[Peo Sjoblom]

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