Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLookup Nightmare!
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 ------------------------------------------------------------------------ twarden's Profile: http://www.excelforum.com/member.php...o&userid=36710 View this thread: http://www.excelforum.com/showthread...hreadid=564423 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLookup Nightmare!
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLookup Nightmare!
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLookup Nightmare!
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLookup a Vlookup | Excel Worksheet Functions | |||
VLOOKUP Problem | Excel Discussion (Misc queries) | |||
Using single cell reference as table array argument in Vlookup | Excel Worksheet Functions | |||
VLOOKUP Limitations | Excel Worksheet Functions | |||
vlookup data hidden within worksheet | Excel Worksheet Functions |