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!!!

 

[RedBeard]

Without caffeine:

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

Does this do the trick?

 

[tygrrboi]

Is JOHNSMITH in the lookup table? Is he being disregarded for a special reason? If he is not on the table... You can do away with your if statements all together as the blank A2 cell will be caught by IFERROR...

=IFERROR(VLOOKUP(A2,Sheet1!$A$2:$AH$143152,34,FALSE), "")

if JOHNSMITH is on your table but you do not want his Vlookup result shown you'll need one IF statement.


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

 

[chrisdeleon]

Hi RedBeard,

Sadly it did not work.

What I am trying to do is:
- compare two different lists of user IDs. List A has user IDs only. List B has user IDs- some are mentioned in List A, some are not- AND info about these users.
- enter the above formula into List A that first checks whether the cell is blank, hence the IF(A2<>"")
-if A2 is not blank, it then checks whether user JOHNSMITH is also not in the list, hence the IF(A2<>"JOHNSMITH")
-next, if both of these conditions are satisfied, then the formula does a Vlookup to identify the common users in Lists A and B and populates info about these users:

The only problem is that the formula returns #N/A for cells that do not have common user IDs and this needs to be blank.

Really appreciate any help you can give!

 

[chrisdeleon]

Hi tygrrboi,

Yes JOHNSMITH is in the lookup table and needs to be disregarded. Tried your second formula but it did not work. Here is some context of what I'm trying to do.

- compare two different lists of user IDs. List A has user IDs only. List B has user IDs- some are mentioned in List A, some are not- AND info about these users.
- enter the above formula into List A that first checks whether the cell is blank, hence the IF(A2<>"")
-if A2 is not blank, it then checks whether user JOHNSMITH is also not in the list, hence the IF(A2<>"JOHNSMITH")
-next, if both of these conditions are satisfied, then the formula does a Vlookup to identify the common users in Lists A and B and populates info about these users:

The only problem is that the formula returns #N/A for cells that do not have common user IDs and this needs to be blank.

Really appreciate any help you can give!

 

[rmorris18921892]

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

This worked fine for me.

 

[tygrrboi]

I do not think I understand. The example formula you gave is only looking at one list... How does the second list factor in?

 

[Aladin Akyurek]

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

This is built on the assumption that VLOOKUP is expected to return a text value if successful.

 

[chrisdeleon]

Thanks ryanmorris18982! This worked. Seems like I was missing the extra ,""),"")

 

[chrisdeleon]

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?