Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup a value between two numbers
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 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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup a value between two numbers
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 " wrote: 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 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup a value between two numbers
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 15,000 <30,000 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup a value between two numbers
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. "Toppers" wrote: 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 " wrote: 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 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need help with V Lookup | Excel Discussion (Misc queries) | |||
Lookup Vector > Lookup Value | Excel Worksheet Functions | |||
Sorting alphanumeric numbers | Excel Discussion (Misc queries) | |||
How do I lookup numbers with a MIN and MAX? | Excel Worksheet Functions | |||
Lookup function w/Text and Year | Excel Worksheet Functions |