VLookup Nightmare!

[twarden]

I'm designing a huge spreadsheet to calculate pension benefits under one
of several types of defined contribution pension plans.

On the "inputs" page, the individual is asked to enter (amongst many
other things):
- # of years until retirement
- Current age
- Age started employment (with the company)
- Current Date
- Start Date (with company)
- pension plan eligibility date
- expected retirement date

this info then feeds into another page which equates the person's age
to each specific day of the year. E.g. on Dec 30th, they were 30.01
years old, on Dec 31st they were 30.011 etc. The list is huge, spanning
35,000 rows over 2 columns (one for the date, one for the individual's
age). A vlookup function is then used to search for the "expected
retirement date" inputted on the previous sheet, and the exact age of
the individual on that date is returned. This works fine 99% of the
time.

Problem: all of the variables above can be entered without screwing up
the formula. However, when # of years untill retirement is changed, the
formula returns #N/A, no matter what that value is changed to. I've
spent 2 hours trying to fix this and have found nothing that works.
PLEASE HELP!

 

[twarden]

the problem described above (returning #N/A) does not occur when
multiples of 4 are entered into the "# of years until retirement"
cell.....any thoughts???

 

[Guest]

Can you post your formula?

 

[Pete_UK]

I don't understand why you need a lookup table to calculate the
person's age or the years to retirement - you can subtract dates
directly, or use functions like DATEDIF.

The multiple of 4 years implies you have a problem recognising leap
years or non-leap years - have you included for this in your table?

Hope this helps.

Pete