Link to home
Start Free TrialLog in
Avatar of j e
j e

asked on

VLOOKUP #NAME? error

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
Avatar of byundt
byundt
Flag of United States of America image

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?
ASKER CERTIFIED SOLUTION
Avatar of byundt
byundt
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Sample file showing both formulas in highlighted yellow cells.
VLOOKUP--NAME-error.xlsx
Avatar of j e
j e

ASKER

Thank you for the help!  It worked perfectly.
je