PDA

View Full Version : Solved: #NA Error with VLookup



Opv
02-15-2010, 02:00 PM
Is there a database size limitation that effects VLookup? Or, are there another constraints which effect the formula that causes the #NA error? I have gone back and double checked my data entries, and the target cell being looked up in the Vlookup formula is present and the specific data element to display is there as well, but I continue to receive an #NA error.

I am testing against a future date. I can't see how that would matter seeing that the date in question is in the database.

Any thoughts?

Bob Phillips
02-15-2010, 02:09 PM
Post the workbook, let's take a look.

Opv
02-15-2010, 02:15 PM
Hmmm. The workbook as some personal, confidential employee information in it. Let me see if I can duplicate the file and replace that information with some dummy information. Thanks.

Paul_Hossler
02-15-2010, 04:42 PM
just a thought, but I've had Vlookup issues that I was SURE should work to find out that there was a trailing space or something that didn't show.

If you're looking up date, try converting the match value to a Double with maybe an Int thrown in for good measure

Paul

Opv
02-15-2010, 05:33 PM
Thanks, Paul. I double checked my data. It is, indeed, a date that is used in the vlookup function, i.e., =VLOOKUP(sourceDate,Data,3,False)

The first column in the spreadsheet range named Data is the Date column against which the sourceDate is matched. There are no stray spaces anywhere that I find. The sourceDate is actually derived from a formula. There are a couple of cells which do not meet the criteria for display on their respective rows and, consequently, the cells result in a blank ("").

The odd thing is that the VLOOKUP function works perfectly all the way up to the February data that is entered. However, when I enter subsequent test rows (resulting in future dates), the #NA error shows up for the March 15, 2010 date.

Strange. Thanks for your help.

Opv

Opv
02-15-2010, 05:46 PM
I figured it out. The VLOOKUP formula is in a different sheet within the workbook. For some reason when the Data (defined name for the area in the target worksheet) name is referenced in the formula, it was not finding the name. I amended the formula as follows: VLOOKUP(targetDate,nameOfSheet!Data,3,False) and it works like a charm. I guess the key is to include the name of the source sheet along with the range name.

Thanks guys,

Opv