Multiple Table Query

[MythicZohar]

OK, its been awhile since I've posted but this group is the best..
Here's my problem:

I am using data validation that is connected to the 2 categories below

Widows Benefits
Widows Benefits with Survivors

Each category will be connected to 2 tables for a total of 4 tables. I
have used VLOOKUP for a similar problem. I named the tables included
the names in the formula..everything worked great.. but with these
tables the return values are in more that 1 column.. Here's what I
mean..


Based on the age of the widow & year since death and the table will
return a value( I have the age calculated in the spreadsheet)

The age of the widow is represented by the 1st column to the left..
and then depending on the number of years since the death of the
spouse for 1,2,3, 4 yrs a value is returned. the tricky part is if the
death of the spouse is 5yrs or greater, then the age is referenced in
the last column and the factor to be used is to the left of the last
column.. I have considered OFFSET, but not sure how that would work

Can someone help me???



Age of Widow on Date of Death of IW YEARS SINCE IW’s DOD
CurrentAge of Widow if DOD of IW>/= 5 Years


CURR YEAR + 1 YEAR + 2 YEARS + 3 YEARS + 4 YEARS + 5 YEARS
16 0.569 0.554 0.507 0.478 0.454 0.431
21
17 0.565 0.549 0.5 0.471 0.446 0.422
22
18 0.56 0.543 0.494 0.463
0.437 0.413 23
19 0.554 0.537 0.486 0.455 0.428 0.402 24
20 0.548 0.531 0.478 0.446 0.418 0.391 25

 

[Pete_UK]

It's always useful to post the formula you are using, together with
the cell references, sheet names, table names etc. However, I think
you can use something like:

MIN(5,number_of_years)+2

as the column to return data from in your VLOOKUP formula.

Hope this helps.

Pete

 

[MythicZohar]

It's always useful to post the formula you are using, together with
the cell references, sheet names, table names etc. However, I think
you can use something like:

MIN(5,number_of_years)+2

as the column to return data from in your VLOOKUP formula.

Hope this helps.

Pete

Thanks Pete.. I'm not a frequent participant so I'm still learning the
etiquette..


The formula I tried was; IF(Fatality!E5<=5,VLOOKUP(Fatality!
E7,Fatality!K4:R93,3))

E5 refers to the number of yrs since death, E7 refers to the age of
the widow. I am inserting the formula's in a separate sheet, ( that
will ultimately be hidden)

I will define each table by name, and insert the appropriate formula
depending on a data validation that I will associate with the 3
categories show below exp Below are the categories and the categories
and the table associations

Widows Benefits
Widows Benefits with Survivors
Marriage Dowry


Widows Benefits-General Business
Widows Benefits with Survivors
– Surviving Spouse Pension Table Self Rater.,Self Rater, Surviving
Spouse Pension Table

Marriage Dowry- General Business – Remarriage Dowry Table, Self Rater
– Remarriage Dowry Table

The returned value will populate in H19, or H20 or H21


Table Names( that are on sheet" fatality" where my validations will
be :

General Business – Remarriage Dowry Table, Self Rater – Remarriage
Dowry Table
General Business – Surviving Spouse Pension Table Self Rater,
Surviving Spouse Pension Table

each table has the same format as shown below, The age of the widow is
in the 1st column and depending on the yr since death that factor is
indicated.
however if 5 yr or more then the age is references in the last column
and the factor used is the factor to the left of the last column.


Age of Widow on Date of Death of IW YEARS SINCE IW’s DEATH Current Age
of Widow if DOD of IW >/= 5 Years
CURR YEAR + 1 YEAR + 2 YEARS + 3 YEARS + 4 YEARS + 5 YEARS
16 0.569 0.554 0.507 0.478 0.454
0.431 21

I hope this is clearer.. and thanks for your help

 

[Pete_UK]

Well, after all that, I think this is the formula you want to change:

IF(Fatality!E5<=5,VLOOKUP(Fatality!E7,Fatality!K4:R93,3))

One drawback with it is what happens if E5 is greater than 5?

You can incorporate what I suggested as follows:

=VLOOKUP(Fatality!E7,Fatality!K$4:R$93,MIN(5,E5)+2))

Note that if E5 is 1 then MIN(5,E5)+2 evaluates as 3 - the column
number where you want to get your return value, If E5 is 2, then the
expression returns 4, and so on up to E5 = 5 and the expression
returns 7.

However, if E5 is, say, 8, then MIN(5,E5)+2 will evaluate as 7, and so
you will always get data from column 7 of your table in this case
(i.e. E5 greater than 5).

Hope this helps.

Pete

 

[MythicZohar]

Well, after all that, I think this is the formula you want to change:

IF(Fatality!E5<=5,VLOOKUP(Fatality!E7,Fatality!K4:R93,3))

One drawback with it is what happens if E5 is greater than 5?

You can incorporate what I suggested as follows:

=VLOOKUP(Fatality!E7,Fatality!K$4:R$93,MIN(5,E5)+2))

Note that if E5 is 1 then MIN(5,E5)+2 evaluates as 3 - the column
number where you want to get your return value, If E5 is 2, then the
expression returns 4, and so on up to E5 = 5 and the expression
returns 7.

However, if E5 is, say, 8, then MIN(5,E5)+2 will evaluate as 7, and so
you will always get data from column 7 of your table in this case
(i.e. E5 greater than 5).

Hope this helps.

Pete

Thanks Pete:

I tried the formula and achieved some functionality however there were
a couple of problems:

- The formula did not return the values for 1-4 yrs
- The 5 yrs or greater age is in last column and the factor is to the
left if the age.

I was thinking I may need to change the table as this is rather
unusual for the age to be in the 1st column and all the factors to the
right expect for the 5 yrs..(who does that???)
is there a way to use offset?