Excel and ranges

[Guest]

My columns look something like this:

min max value
1 500 1
501 1000 1.2
1001 2000 1.7
2001 5000 2.3

Now, based on that, I want to have cell that allows you to enter a number.
Using that number, the cell next to it needs to determine the range the
entered value is in and return the value from the list about. Therefore, if
I enter 550, the next cell over would return 1.2. If I enter 497, it would
return 1.

I have a lame method that works now using if statements within if statements
but when I have more then 9 ranges, Excel won't work because Excel has a
limit of 9 embedded if statements. I need to support up to 20 or so ranges
as opposed to the 4 ranges above.

If someone can help me out, I'd appreciate it a TON! I'm fairly well versed
in Excel so get as technical as you'd like. If VBA is the only easy answer,
then I can do that without help. I just wanted to get around using VBA if at
all possible.

Thanks in advance!

 

[Dave Peterson]

How about a table on sheet2 that looks like:

0 1
501 1.2
1001 1.7
2001 2.3


And then just use
=vlookup(a1,sheet2!a:b,2)

 

[Chris Lavender]

You should use VLOOKUP with 1 as the final argument, ie

=vlookup(targetcell,A2:C5,3,1) assuming 'min' is in cell A1

Using 1 as the final argument tells XL to find the nearest match rather than
an exact match for eg, 497. Your min column needs to be in ascending order,
though.

Best rgds
Chris Lav

 

[Guest]

Thanks a TON...that works great!

You should use VLOOKUP with 1 as the final argument, ie

=vlookup(targetcell,A2:C5,3,1) assuming 'min' is in cell A1

Using 1 as the final argument tells XL to find the nearest match rather than
an exact match for eg, 497. Your min column needs to be in ascending order,
though.

Best rgds
Chris Lav