lookup range of numbers; return corresponding data for the fixed r

[Guest]

I have a spreadsheet that I am using to do a breakeven analysis using
different assumptions. One of the assumptions is "start age" which needs to
be able to change. However, depending on where age 70 through age 100 falls
when "start age is changed," I need the next column to identify age 70 and
fill in 1.2, age 71 and fill in 3.5, age 72 and fill in 5.4, etc. There are
fixed numbers that don't have a formula for each year through age 100. EX:

Age RMD Factor

A1 65
A2 66
A3 67
A4 68
A5 69
A6 70 1.2
A7 71 3.5
A8 72 5.4
A9 73 6.9
A10 74 7.8
A11 75 8.1
A12 76 8.5

The start age is 65, but if it was changed to 60, the RMD factor would no
longer correspond with 70. Is there any way to do this?

 

[Guest]

Put the RMD factors in a table on a separate sheet with Age & RMD factor


e.g Sheet2
Col A Col B
70 1.2
71 3.5
72 5.4

etc

Then use VLOOKUP

Put in B1 and copy down

=If("age" <70,"",VLOOKUP("Age",Sheet2!A:B,2,0)

where "age" is your A column cell