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

Microsoft ExcelMicrosoft Office

Last Comment

j e

8/22/2022 - Mon

byundt

1/22/2020

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

byundt

1/22/2020

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

GET A PERSONALIZED SOLUTION

Ask your own question & get feedback from real experts