How to tell if a value is between two other values from a list

[TheQ47]

I have a spreadsheet in Excel 2007 (on Windows Vista) which has two columns of numbers (they're actually currency amounts) similar to this:

A............B
0.00......30.00
30.01.....32.50
32.51.....35.00
35.01.....37.50
37.51.....40.00
40.01.....42.50
...

And so on, up to
242.50.....245.00
Over 245.00

On another sheet of the same Spreadsheet, the user will be generating a number based on other (unrelated) inputs. This number could be any currency amount.

What I would like to be able to do is use a formula to find where the generated number falls in the list. Where this generated number is greater than the number in Column A and less than or equal to the number in column B, the result of the formula will be the value in Column B.

Here's an example, The user generates a number, 36.46, which falls between the numbers in row 4, i.e., 35.01 - 37.50. I would like the formula to return the result 37.50.

If at all possible, I'd like to be able to do it using a formula rather than VBA.

I've tried using various combinations of CHOOSE, IF, AND, VLOOKUP, MEDIAN, and many others, so much that I've completely lost track of where to go now. Any help will be much appreciated.

 

[RSXchin]

look at the "LOOKUP" function. I'll actually try to make it pretty for you. Are the values ALWAYS those you have posted? or do they change?

 

[number kruncher]

I would use the VLOOKUP as..

=VLOOKUP(number,A:B,2,TRUE)

 

[TheQ47]

look at the "LOOKUP" function. I'll actually try to make it pretty for you. Are the values ALWAYS those you have posted? or do they change?

Thanks for the speedy response, LOOKUP seems to be perfect! I'll try it out immediately, I'd never heard of LOOKUP before.

You asked if the numbers change, and the answer is no, or at least very rarely, so rarely that it doesn't matter.

 

[RSXchin]

Changed my mind. Vlook up is perfect for you.

=VLOOKUP(C1,$A:$B,2)

C1 is the cell you are checking. Change it to suit


AWWWWWWWWWWWW

 

[T. Valko]

Changed my mind. Vlook up is perfect for you.

=VLOOKUP(C1,$A:$B,2)

C1 is the cell you are checking. Change it to suit


AWWWWWWWWWWWW

You had it right the first time!

=LOOKUP(C1,A2:B7)

 

[TheQ47]

I would use the VLOOKUP as..

=VLOOKUP(number,A:B,2,TRUE)

Changed my mind. Vlook up is perfect for you.

=VLOOKUP(C1,$A:$B,2)

C1 is the cell you are checking. Change it to suit


AWWWWWWWWWWWW

Duh! I have always used VLOOKUP with FALSE as the last argument, and never really thought of changing that. It was that simple. Thanks again, people!

 

[RSXchin]

I was a bit upset that I got it in 2 seconds after the other guy (so to speak).

 

[TheQ47]

I was a bit upset that I got it in 2 seconds after the other guy (so to speak).

I've tried both LOOKUP and VLOOKUP, and they both seem to work correctly, thanks a million. I've passed them both on to the guy who was asking me to sort it out for him, he now thinks I'm an Excel genius

I'll be back!

 

[RSXchin]

how did you setup the lookup function? I was having troubles with the syntax.