using vlookup for a range of cells

[Guest]

I'm having a difficult time with the vlookup function. Here's what I have: I
am working on a spreadsheet for a hypothetical situation where at the top of
the spreadsheet, I have a box designated for a number of assumptions. One of
the assumptions is starting age. Then I have the following:

Year Age RMD factor
1 65
2 66
3 67
4 68
5 69
6 70 27.4
7 71 26.3
8 72 25.6

etc. all the way through age 100. Now, for every age number from 70 to 100,
there is an RMD factor. In the above example, the starting age is 65. I put
the data into a separate sheet for only age 70 through 100 and the
corresponding RMD factor, in 2 separate columns. What I'm having trouble
with is when "start age" changes, 27.4 will no longer be with age 70, 26.3
will no longer be with age 71, and so on, and I need it to be. I have
embedded a vlookup function into the value_if_false area of an if statement,
with the logical test being B1,70. This works, but it still does not match
70 with 27.4 if the start age changes. Can anyone help???

 

[George Nicholson]

Age changes. Birthdate does not. Sounds like you might want to structure
your lookup on a static birthdate, not an evolving age.

example, replacing 70 in your table with:
=DATE(YEAR(TODAY())-70,MONTH(TODAY()),DAY(TODAY()))
Result: May 4 1937. Then have Vlookup use Birthdate rather than age (and to
find approximate matches).

Depending on what Age represents (or how its calculated), you might be able
to get away with birthyear rather than full birthdate.

HTH,

 

[T. Valko]

I'm having trouble understanding this:

when "start age" changes, 27.4 will no longer be with age 70,
26.3 will no longer be with age 71, and so on, and I need it to be

Maybe this:

=IF(B1>=70,VLOOKUP(B1,Sheet2!A$1:B$30,2,0),"")

Biff