Hi,
I am creating a spreadsheet where basically various criteria calculate based off the input of the client's date of birth. What I am trying to do is get the DOB to tell excel to pull information from a table into the specified cell.
For example, "full retirement age" for a client born after 1960 would be 67. But Full retirement age for a client born in 1959 would be 66 and 10 months. Full retirement age for someone born in 1958 would be 66 and 8 months, etc. I want to be able to enter the DOB in excel and it populate in the "FRA" cell the correlated value from the table.
As you can see from the (attached) worksheet, there is a formula in the "FRA Ret. Date" column that will then add the year and # of months together (yr and number of months being what I want to pull from the table based on the DOB entry) to calculate the FRA Ret. Date.
I have uploaded the spreadsheet that I have created for reference.
Any help would be most appreciated!
Thanks,
Charlie
Hi Charlie
Basically what you need is the number of months to be added to the DOB to get the FRA date.
What I did was to convert your lookup table from Years-Months to Months so that is is easier for the VLOOKUP to search for only 1 value (months) instead of 2 (years and months)
I then use a VLOOKUP with the last argument set to TRUE. This will allow me to lookup for the years between 1943 and 1954 without having to key-in these years one by one into the table.
I then use EDATE to add the months to the DOB to get the FRA.
Please note that the earliest possible Year for the DOB is 1938 (as per your lookup table).
Hope this helps.
Sunny
