Removing #N/A from result of two IF statements and a Vlookup

[chrisdeleon]

Hi all,

I am trying to remove/leave blank the #N/A result of a formula containing two nested IF statements and a Vlookup. The current formula is:

=IF(A2<>"",IF(A2<>"JOHNSMITH",VLOOKUP(A2,'Sheet1'!$A$2:$AH$143152,34,FALSE),""),"")

I've tried placing an IFERROR before the entire formula as well as placing an ISNA before the Vlookup but neither approach works for me.

I really just want the result of the formula to return a blank cell.

Thanks for any help you can provide!!!

 

[Aladin Akyurek]

Thank you Aladin Akyurek! I was just looking solution you provided on another topic and it worked! I used:

=IF(A2<>"",IF(A2<>"JOHNSMITH",IF(ISNA(VLOOKUP(A2,Sheet1!$A$2:$AH$143152,34,FALSE)),"",VLOOKUP(A2,Sheet1!$A$2:$AH$143152,34,FALSE)),"","")

Does this make sense?

Yes, it does make sense, in particular if you don't have iferror or ifna. what does your vlookup return - text?

 

[chrisdeleon]

The vlookup returns text, yes.

 

[Aladin Akyurek]

The vlookup returns text, yes.

In that case, one of the following would also work...

=IF(OR(A2="",A2="JOHNSMITH"),"",IFNA(T(VLOOKUP(A2,Sheet1!$A$2:$AH$143152,34,0)),""))

=IF(OR(A2="",A2="JOHNSMITH"),"",IFERROR(T(VLOOKUP(A2,Sheet1!$A$2:$AH$143152,34,0)),""))

You get here also a blank if A2 is available but there is no associated value.