software

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
gt;15,000 to 30,000 = 1
gt;30,000 to 50,000 = 2

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 DonSet up table as below Columns A amp; B). Table must be in descending order with
large number at top to catch values up to your maximum. In example
maxlt;=9999999

A B
99999993
500002
300001
150000

Assume lookup value is in C1 then in D1 put:

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

HTH

quot; wrote:

gt; If you have two values that equals a certain value, now let says the
gt; value that you have falls in between these two values, how do you get
gt; excel to reconize this and return the correct value? For instance:
gt;
gt; 0 to 15,000 = 0
gt; gt;15,000 to 30,000 = 1
gt; gt;30,000 to 50,000 = 2
gt;
gt; My table I have layed out with each value assigned to separate cells.
gt; Is this the correct way to lay this table out? In addition, In order
gt; to see if I could get the result I need I did a VLOOKUP and the formula
gt; returned a #N/A error. What am I doing wrong? Appreciate some help,
gt; Thanks Don
gt;
gt;

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 amp; 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
gt;15,000 lt;30,000If 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 quot;Cut-offquot; points in column A and the quot;Valuequot; (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.

quot;Toppersquot; wrote:

gt; Set up table as below Columns A amp; B). Table must be in descending order with
gt; large number at top to catch values up to your maximum. In example
gt; maxlt;=9999999
gt;
gt; A B
gt; 99999993
gt; 500002
gt; 300001
gt; 150000
gt;
gt; Assume lookup value is in C1 then in D1 put:
gt;
gt; =INDEX($A$1:$B$4,MATCH(C1,$A$1:$A$4,-1),2)
gt;
gt; HTH
gt;
gt; quot; wrote:
gt;
gt; gt; If you have two values that equals a certain value, now let says the
gt; gt; value that you have falls in between these two values, how do you get
gt; gt; excel to reconize this and return the correct value? For instance:
gt; gt;
gt; gt; 0 to 15,000 = 0
gt; gt; gt;15,000 to 30,000 = 1
gt; gt; gt;30,000 to 50,000 = 2
gt; gt;
gt; gt; My table I have layed out with each value assigned to separate cells.
gt; gt; Is this the correct way to lay this table out? In addition, In order
gt; gt; to see if I could get the result I need I did a VLOOKUP and the formula
gt; gt; returned a #N/A error. What am I doing wrong? Appreciate some help,
gt; gt; Thanks Don
gt; gt;
gt; gt;