using vlookup for a range of cells
 

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???

using vlookup for a range of cells
 

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,

"mmpfa07" wrote in message
...
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???

using vlookup for a range of cells
 

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

"mmpfa07" wrote in message
...
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???