IFNA nested into an if statement w/ a vlookup also nested

[hmsmith54]

I have an if statement to tell me if there is a specific value "CAD" and if it equals CAD to do a vlookup to pull in a dollar amount based on a value in another cell. This I have working fine, however, I need to go one step further and if the error #N/A comes up I need it to pull in a 0.

This is my beginning formula

=IF(G2="CAD",VLOOKUP(C2,INVOICE,5,FALSE), "Not CAD ")

I do not know where (or if it is even possible) to add an if iserror or and IFNA statement to pull in 0.

Any help would be greatly appreciated!

 

[konew1]

=if(isna(IF(G2="CAD",VLOOKUP(C2,INVOICE,5,FALSE), "Not CAD ")),0,IF(G2="CAD",VLOOKUP(C2,INVOICE,5,FALSE), "Not CAD "))

 

[hmsmith54]

=if(isna(IF(G2="CAD",VLOOKUP(C2,INVOICE,5,FALSE), "Not CAD ")),0,IF(G2="CAD",VLOOKUP(C2,INVOICE,5,FALSE), "Not CAD "))

Thank you so very much! I really appreciate the help!

Hope you have a great day!

 

[Rasscal]

=if(isna(IF(G2="CAD",VLOOKUP(C2,INVOICE,5,FALSE), "Not CAD ")),0,IF(G2="CAD",VLOOKUP(C2,INVOICE,5,FALSE), "Not CAD "))

i know this is an old post but my formula is very similar with a similar issue, but instead of putting a 0 value in the cell i would like to vlookup a different range if #N/A

=IF(Q4="Yes",VLOOKUP(W4,'[FY18-FY19 PPD.xlsx]FY18-FY19 PPD'!$S$2:$T$1048576,2,FALSE),"Non WH Error")


eg
=IF(Q4="Yes",VLOOKUP(W4,'[FY18-FY19 PPD.xlsx]FY18-FY19 PPD'!$S$2:$T$1048576,2,FALSE),"Non WH Error") = #N/A then VLOOKUP(X4,'[FY18-FY19 PPD.xlsx]FY18-FY19 PPD'!$S$2:$T$1048576,2,FALSE),"Non WH Error")

i hope this makes sense.

 

[AlanY]

try

=IF(Q4="Yes",IFERROR(VLOOKUP(W4,'[FY18-FY19 PPD.xlsx]FY18-FY19 PPD'!$S$2:$T$1048576,2,FALSE),VLOOKUP(X4,'[FY18-FY19 PPD.xlsx]FY18-FY19 PPD'!$S$2:$T$1048576,2,FALSE)),"Non WH Error")

 

[Rasscal]

try

=IF(Q4="Yes",IFERROR(VLOOKUP(W4,'[FY18-FY19 PPD.xlsx]FY18-FY19 PPD'!$S$2:$T$1048576,2,FALSE),VLOOKUP(X4,'[FY18-FY19 PPD.xlsx]FY18-FY19 PPD'!$S$2:$T$1048576,2,FALSE)),"Non WH Error")

yes that works

I have so many varibles in my data its crazy, the above works for 90% of my data but i have an issue, can i add another vlookup in there?

eg.

=IF(Q4="Yes",IFERROR(VLOOKUP(W4,'[FY18-FY19 PPD.xlsx]FY18-FY19 PPD'!$S$2:$T$1048576,2,FALSE),VLOOKUP(X4,'[FY18-FY19 PPD.xlsx]FY18-FY19 PPD'!$S$2:$T$1048576,2,FALSE)),"Non WH Error") IF result = "system" then VLOOKUP(Y4,'[FY18-FY19 PPD.xlsx]FY18-FY19 PPD'!$S$2:$T$1048576,2,FALSE)),"Non WH Error")

thanks for your help

 

[AlanY]

same idea, just extend the iferror() function

=IF(Q4="Yes",IFERROR(VLOOKUP(W4,'[FY18-FY19 PPD.xlsx]FY18-FY19 PPD'!$S$2:$T$1048576,2,FALSE),IFERROR(VLOOKUP(X4,'[FY18-FY19 PPD.xlsx]FY18-FY19 PPD'!$S$2:$T$1048576,2,FALSE),VLOOKUP(Y4,'[FY18-FY19 PPD.xlsx]FY18-FY19 PPD'!$S$2:$T$1048576,2,FALSE))),"Non WH Error")

 

[Rasscal]

same idea, just extend the iferror() function

=IF(Q4="Yes",IFERROR(VLOOKUP(W4,'[FY18-FY19 PPD.xlsx]FY18-FY19 PPD'!$S$2:$T$1048576,2,FALSE),IFERROR(VLOOKUP(X4,'[FY18-FY19 PPD.xlsx]FY18-FY19 PPD'!$S$2:$T$1048576,2,FALSE),VLOOKUP(Y4,'[FY18-FY19 PPD.xlsx]FY18-FY19 PPD'!$S$2:$T$1048576,2,FALSE))),"Non WH Error")

im confused

because the third criteria isnt an error the result is "system" (from my data) & your latest sugestion doesnt work im afraid.

if this cell is yes vlookup against 1st set of data (result username or #N/A)
if this is error vlookup against 2nd set of data (result username or "system")
if this is "system" vlookup against 3rd set of data(result username)

 

[AlanY]

same idea, just extend the iferror() function

sorry didn't read your post properly, again!

 

[AlanY]

im confused

because the third criteria isnt an error the result is "system" (from my data) & your latest sugestion doesnt work im afraid.

if this cell is yes vlookup against 1st set of data (result username or #N/A)
if this is error vlookup against 2nd set of data (result username or "system")
if this is "system" vlookup against 3rd set of data(result username)

may be this

=IF(Q4="Yes",IFERROR(VLOOKUP(W4,'[FY18-FY19 PPD.xlsx]FY18-FY19 PPD'!$S$2:$T$1048576,2,FALSE),IF(VLOOKUP(X4,'[FY18-FY19 PPD.xlsx]FY18-FY19 PPD'!$S$2:$T$1048576,2,FALSE)="System",VLOOKUP(Y4,'[FY18-FY19 PPD.xlsx]FY18-FY19 PPD'!$S$2:$T$1048576,2,FALSE),VLOOKUP(X4,'[FY18-FY19 PPD.xlsx]FY18-FY19 PPD'!$S$2:$T$1048576,2,FALSE))),"Non WH Error")