Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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??? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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??? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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??? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
CAN VLOOKUP SEARCH AN ENTIRE WORKSHEET INSTEAD OF RANGE OF CELLS | Excel Worksheet Functions | |||
Use VLookup for range of cells, but with a twist | Excel Worksheet Functions | |||
how to compute a range of cells based on another range of cells? | Excel Worksheet Functions | |||
how to compute a range of cells based on another range of cells? | Excel Worksheet Functions | |||
Count cells in one range based on parameters in another range | Excel Worksheet Functions |