We help IT Professionals succeed at work.

VLOOKUP #NAME? error

j e
j e asked
on
I am using a nested VLOOKUP - against two look-up areas....  It works for part of it - but then I get #NAME? error when not found on either.  I've looked at the formula over and over but can't see where I'm off.  

Logic:

if item in column k is not found in first area, then check to see if item in column l is found in second area.
Obtain value if found in one of the two areas.
Write out 'value not found' if nothing found in either.

Here's the formula:

=IF(ISNA(VLOOKUP(K5,tbl_Process_File_Counts!$D$1:$G$51,4,FALSE)), IF(ISNA(VLOOKUP(L5,tbl_Process_File_Counts2!$E$1:$G$51,3,FALSE)),”count Note Found”, VLOOKUP(L5,tbl_Process_File_Counts2!$E$1:$G$51,3,FALSE)), VLOOKUP(K5,tbl_Process_File_Counts!$D$1:$G$51,4,FALSE))


Thanks for your help,
je
Comment
Watch Question

byundtMechanical Engineer
Most Valuable Expert 2013
Top Expert 2013

Commented:
I reproduced your #NAME? error when data was not present on either worksheet. I fixed it by replacing the curly quotes surrounding "count Note found" with straight double quotes. In the formula below, I decided you meant "Not" rather than "Note".
=IF(ISNA(VLOOKUP(K5,tbl_Process_File_Counts!$D$1:$G$51,4,FALSE)), IF(ISNA(VLOOKUP(L5,tbl_Process_File_Counts2!$E$1:$G$51,3,FALSE)),”count Not Found”, VLOOKUP(L5,tbl_Process_File_Counts2!$E$1:$G$51,3,FALSE)), VLOOKUP(K5,tbl_Process_File_Counts!$D$1:$G$51,4,FALSE))

Open in new window


Also, did you mean to search worksheets rather than actual Tables? And did you mean to search first for K5 and then for L5?
Mechanical Engineer
Most Valuable Expert 2013
Top Expert 2013
Commented:
If you are using Mac Excel 2011/Windows Excel 2013 or later, you have the IFNA function available to you. Using it allows you to build a much simpler formula without need to duplicate the VLOOKUP calls.
=IFNA(VLOOKUP(K5,tbl_Process_File_Counts!$D$1:$G$51,4,FALSE), 
 IFNA(VLOOKUP(L5,tbl_Process_File_Counts2!$E$1:$G$51,3,FALSE), "count Not Found"))

Open in new window

byundtMechanical Engineer
Most Valuable Expert 2013
Top Expert 2013

Commented:
Sample file showing both formulas in highlighted yellow cells.
VLOOKUP--NAME-error.xlsx
j e

Author

Commented:
Thank you for the help!  It worked perfectly.
je