If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Weighted avg of numbers not in contiguous row-ie array w lookup? | Diane | Worksheet Functions | 2 | November 9th, 2005 02:12 AM |
Provide an option to remove fax numbers from Outlook lookup | jbl20001 | Contacts | 1 | November 2nd, 2005 06:37 PM |
Match Last Occurrence of two numbers and Return Date | Sam via OfficeKB.com | Worksheet Functions | 6 | April 5th, 2005 12:40 PM |
Match Last Occurrence of two numbers and Count to Previous Occurence | Sam via OfficeKB.com | Worksheet Functions | 33 | April 4th, 2005 02:17 PM |
Lookup - Formatting of numbers | GRS | General Discussion | 6 | August 2nd, 2004 11:31 PM |