I understand most of the formula from the response to my original posting
(see orig posting and response below) except for the following:

MATCH(B4,OFFSET(Table2,1,1,,1),TRUE)+1

How does this find the row where the age in Table 1 is between Age_lower and
Age_upper in Table2?  What does +1 do?

Thanks,  Attila

Original Posting*****************************

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

Response*******************************

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.