Complex Lookup Question - Clarification?????

[Guest]

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.

 

[Guest]

Try this:
This formula probably much easier for you to understand

Table 1 Criteria:
B2: holds Age
C2: holds Status

Create defined name ranges for table 2

=SUMPRODUCT((Age_lower<=B2)*(Age_upper>=B2),INDIRECT(C2))

 

[JE McGimpsey]

This uses an approximate match (hence the TRUE) on the rows starting
below the header row in the second column of Table2 (in the OFFSET()
function, the first 1 offsets the rows by 1, and the second 1 offsets
the columns by 1). The last 1 in the OFFSET() limits the Match to 1
column.

The +1 at the end is to account for having offset the lookup column in
the MATCH by one (the header) row when indexing the original table.