Reputation: 113
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
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
Reputation: 152660
Use this:
=IF(B2="","",IF(ISNA(VLOOKUP(B2,Dists!$D$1:$D$22250,1,FALSE)),"NOT FOUND","MATCHES"))
Upvotes: 2