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

[tim m]

Can you post your formula?

"twarden" wrote:

>
> 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???
>
>
> --
> twarden
> ------------------------------------------------------------------------
> twarden's Profile: http://www.excelforum.com/member.php...o&userid=36710
> View this thread: http://www.excelforum.com/showthread...hreadid=564423
>
>

[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

twarden wrote:
> 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???
>
>
> --
> twarden
> ------------------------------------------------------------------------
> twarden's Profile: http://www.excelforum.com/member.php...o&userid=36710
> View this thread: http://www.excelforum.com/showthread...hreadid=564423