Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Complex Lookup question.
I have to do a complex lookup. Here are the excel tables involved:
Table 1 Name Age Status Jim 25 MNS Mary 38 FS Table 2 Band Age_lower Age_upper MS MNS FS FNS 1 0 18 0.25 0.2 0.18 0.14 2 19 29 1.25 1.2 1.18 1.14 3 30 39 2.25 2.2 2.18 2.14 4 40 49 3.25 3.2 3.18 3.14 5 50 59 4.25 4.2 4.18 4.14 Here is what I have to do: 1) Based on the age of the individual in Table 1 I need to determine the Band in Table 2 - this is determined by looking at their age and finding which row their age is between Age_lower and Age_upper. 2) Once I found the Band I then look at their Status in Table 1 and then find select the appropriate value from the Band row in table 2 For example, for Jim in Table 1 he would fall in Band 2 and based on his status of MNS the value selected would be 1.2 (from row2,col5 in Table 2). Mary would be band 3 and value of 2.14. Is this possible with one formula? Please let me know. Thanks, Attila |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Complex Lookup question.
One way:
=INDEX(Table2, MATCH(B2,OFFSET(Table2,1,1,,1),TRUE)+1, MATCH(C2,OFFSET(Table2,,,1,),FALSE)) This assumes that table 2 is named Table2 (including the header row and band column), that B2 refers to the Age in Table 1, and C2 is the Status. Note that it will give a bad result if Age is the maximum Age_Upper. In article , bevpike wrote: I have to do a complex lookup. Here are the excel tables involved: Table 1 Name Age Status Jim 25 MNS Mary 38 FS Table 2 Band Age_lower Age_upper MS MNS FS FNS 1 0 18 0.25 0.2 0.18 0.14 2 19 29 1.25 1.2 1.18 1.14 3 30 39 2.25 2.2 2.18 2.14 4 40 49 3.25 3.2 3.18 3.14 5 50 59 4.25 4.2 4.18 4.14 Here is what I have to do: 1) Based on the age of the individual in Table 1 I need to determine the Band in Table 2 - this is determined by looking at their age and finding which row their age is between Age_lower and Age_upper. 2) Once I found the Band I then look at their Status in Table 1 and then find select the appropriate value from the Band row in table 2 For example, for Jim in Table 1 he would fall in Band 2 and based on his status of MNS the value selected would be 1.2 (from row2,col5 in Table 2). Mary would be band 3 and value of 2.14. Is this possible with one formula? Please let me know. Thanks, Attila |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Complex Lookup question.
works great, I just need to figure out how it works - I will do some resarch
Thanks, Attila "JE McGimpsey" wrote: One way: =INDEX(Table2, MATCH(B2,OFFSET(Table2,1,1,,1),TRUE)+1, MATCH(C2,OFFSET(Table2,,,1,),FALSE)) This assumes that table 2 is named Table2 (including the header row and band column), that B2 refers to the Age in Table 1, and C2 is the Status. Note that it will give a bad result if Age is the maximum Age_Upper. In article , bevpike wrote: I have to do a complex lookup. Here are the excel tables involved: Table 1 Name Age Status Jim 25 MNS Mary 38 FS Table 2 Band Age_lower Age_upper MS MNS FS FNS 1 0 18 0.25 0.2 0.18 0.14 2 19 29 1.25 1.2 1.18 1.14 3 30 39 2.25 2.2 2.18 2.14 4 40 49 3.25 3.2 3.18 3.14 5 50 59 4.25 4.2 4.18 4.14 Here is what I have to do: 1) Based on the age of the individual in Table 1 I need to determine the Band in Table 2 - this is determined by looking at their age and finding which row their age is between Age_lower and Age_upper. 2) Once I found the Band I then look at their Status in Table 1 and then find select the appropriate value from the Band row in table 2 For example, for Jim in Table 1 he would fall in Band 2 and based on his status of MNS the value selected would be 1.2 (from row2,col5 in Table 2). Mary would be band 3 and value of 2.14. Is this possible with one formula? Please let me know. Thanks, Attila |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Complex Lookup | Excel Discussion (Misc queries) | |||
Complex Lookup | Excel Worksheet Functions | |||
Complex lookup | Excel Worksheet Functions | |||
Complex LookUp / Match Problem ?? | Excel Worksheet Functions | |||
complex lookup | Excel Discussion (Misc queries) |