B
bevpike
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
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