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.531 0.478 0.446
0.418 0.391 25

 

[Rajesh Mehmi]

Try the following


=IF(YRS>=5,VLOOKUP(AGE,FACTORTABLE,MIN(6,YRS),FALSE),VLOOKUP(AGE,FACTORTABLE,1+YRS,FALSE))


=IF(YRS>=5,VLOOKUP(AGE,FACTORTABLE,IF(YRS>=5,6,YRS),FALSE),VLOOKUP(AGE,FACTORTABLE,1+YRS,FALSE))



Years
Years Years Years Years Years
age Years since Factor Age 1 2
3 4 5 6
18 5 0.437 16 0.569 0.554
0.507 0.478 0.454 0.431
17 0.565
0.549 0.500 0.471 0.446 0.422
18 0.560
0.543 0.494 0.463 0.437 0.413

age = age of spouse
Years since = years since members death



Best regards

Rajesh Mehmi

(e-mail address removed)








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.531 0.478 0.446
0.418 0.391 25