Problems with nested IFs and #N/A errors

Question

I have a 3 tab worksheet labelled Current Customers, Old Customers and Order Form respectively. 

The Current Customers and Old Customers tabs each have 3 columns - Cust#, First Name and Last Name.

I have 5 cells on Order Form labelled Cust#, First Name, Last Name, New First Name and New Last Name.

When I enter data in the Cust # field I want the First Name and Last Name fields to auto-populate. 

First I want it to Index/Match the Current Customers tab. 

If it does not find the Customer number then I want it to Index/Match the Old Customers tab. 

If it still does not find a match then I want it to use the information in the New First Name and New Last Name fields. 

I can do the Index/Match and nested IFs, but my problem occurs when I get the #N/A errors.  The IFNA does not give me two options that can both be formulated.  The IFs consider #N/A a valid output.  Any help with the proper sequence and formulas to make this work would be greatly appreciated.

Answer 1

Hi jwelch176

Please find and download from the link below a file with the answer to your question.

https://www.dropbox.com/s/a1jtu82jy0pptdb/jwelch176-answer.xlsx?dl=0

First Name Formula =IF($B$1="","",IFNA(VLOOKUP($B$1,'Current Customers'!$A$1:$C$11,2,0),IFNA(VLOOKUP($B$1,'Old Customers'!$A$1:$C$11,2,0),$B$4)))

Last Name Formula =IF($B$1="","",IFNA(VLOOKUP($B$1,'Current Customers'!$A$1:$C$11,3,0),IFNA(VLOOKUP($B$1,'Old Customers'!$A$1:$C$11,3,0),$B$5)))

***************************************************************************************************

Do let me know if you require any further help on this. Will be glad to help you.

If this answer would be the solution to your question, I'll appreciate you mark it as answered