CODEWITHSUNDEEP
excelexcel-formula
Xiodrade
Xiodrade

Reputation: 113

Excel Multiple if arguements

My formula:

=IF(ISNA(VLOOKUP(B2,Dists!$D$1:$D$22250,1,FALSE) & AND(B2<>"")),"","MATCHES")

This essentially looks at B2 to see if it returns a VLOOKUP value and if it's blank. If it's not blank, and VLOOKUP returns a value, then it displays "MATCHES" in the designated cell. If it does not return a value or it is blank, it displays the cell as blank.

I would like to expand on this, but I can't figure it out for the life of me. I am trying to have a cell display blank if B2 is blank, but display "NOT FOUND" if it's not blank and the VLOOKUP returns false. It will display "MATCHES" if the vlookup returns a value.

For example..

B2 is blank so my cell shows as blank. B2 is not blank and VLOOKUP returns true, so my cell says MATCHES. B2 is not blank and VLOOKUP returns false, so my cell says NOT FOUND.

Any clue?

Upvotes: 0

Views: 56

Answers (2)

Fritz
Fritz

Reputation: 624

While your code has other structural errors resolved in the answer provided by Scott, your original function =IF(ISNA(VLOOKUP(B2,Dists!$D$1:$D$22250,1,FALSE) & AND(B2<>"")),"","MATCHES") uses the AND() function incorrectly.

AND(), OR(), XOR(), and NOT() in Excel are placed before the different condition. For example, =IF(OR(A1="Foo",A1="Bar"),"Yes","No") returns YES when A1 contains either Foo or Bar.

Microsoft Office Support - AND() function

Upvotes: 0

Scott Craner
Scott Craner

Reputation: 152660

Use this:

=IF(B2="","",IF(ISNA(VLOOKUP(B2,Dists!$D$1:$D$22250,1,FALSE)),"NOT FOUND","MATCHES"))

Upvotes: 2

Related Questions