Lookup a value between two numbers

B

Braenovich.Don

If you have two values that equals a certain value, now let says the
value that you have falls in between these two values, how do you get
excel to reconize this and return the correct value? For instance:

0 to 15,000 = 0
15,000 to 30,000 = 1
30,000 to 50,000 = 2
Click to expand...

My table I have layed out with each value assigned to separate cells.
Is this the correct way to lay this table out? In addition, In order
to see if I could get the result I need I did a VLOOKUP and the formula
returned a #N/A error. What am I doing wrong? Appreciate some help,
Thanks Don
 
T

Toppers

Set up table as below Columns A & B). Table must be in descending order with
large number at top to catch values up to your maximum. In example
max<=9999999

A B
9999999 3
50000 2
30000 1
15000 0

Assume lookup value is in C1 then in D1 put:

=INDEX($A$1:$B$4,MATCH(C1,$A$1:$A$4,-1),2)

HTH
 
B

Braenovich.Don

Topper thanks for the response, lets see if I'm clear do I put the two
range figures in two separate cells as below and there result in a cell
as well? Now the formula you show above will distinguish between the
range of Column A & B, if I have a value of 12,000 and the formula
result will be 0? Thanks for your help, Don

A B C
0 15,000 0
 
T

Toppers

If I understood correctly:

For any value between 0 and 15000, you want 0 returned. For 15001 to 30000
you want 1, etc ?

If this is correct, then you just set up the table as my previous posting
with the "Cut-off" points in column A and the "Value" (0,1,2) in column B.

Column C1 contains 12000 so the formula placed in D1 should return 0; if C1
contains 24500 it should return 1.

If I have completely misunderstood, my apologies.
 
Top