Thread
:
Multiple Table Query
View Single Post
#
2
Posted to microsoft.public.excel.newusers
Rajesh Mehmi
external usenet poster
Posts: 26
Multiple Table Query
Try the following
=IF(YRS=5,VLOOKUP(AGE,FACTORTABLE,MIN(6,YRS),FALS E),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
"MythicZohar" wrote in message
...
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
Reply With Quote
Rajesh Mehmi
View Public Profile
Find all posts by Rajesh Mehmi