Excel - return "true" if a value exists in both columns

Question

I have two columns of data. column A and column B. in the third column (C) I want to type a formula that will return "true" if a data in column B exists anywhere in column A. Just this :) Thank you in advance.

Solution

Try This:

=NOT(ISNA(VLOOKUP(B1,A:A,1,0)))

Assuming you are starting in cell C1.

VLOOKUP returns the value of B1 in the column A, or #N/A if it's not found. ISNA will return TRUE if no value is found, or FALSE if it is, finally NOT negates the result such that it will return TRUE if value is found and FALSE otherwise.

Comments

[Billef32]

I don't know what is going on :/ It's no working good for me. I tried this =NOT(ISNA(VLOOKUP(B5;A$1:A$269;0))) I put the dollar in order to lock the array. In order to test the formula i have two identical columns with names. A and B columns. In the C column I typed the formula. I should get true for each name since B it's a copy-paste of A. But not only I get False on some of them but if I delete a cell from Column A it doesn't change the result!

[Jerry]

@Billef32 If it's a copy paste, does =A5=B5 return true for the values the vlookup is returning false? (change the reference as necessary)

[Firefly]

You are missing an argument in the VLOOKUP, try: =NOT(ISNA(VLOOKUP(B5;A$1:A$269;1;0)))

[Billef32]

Yes while =A1=B1 returns true.. the formula above is returning false in some cases! I don't know why. A5 :Adriana Colman B5 :Adriana Colman C5 :=NOT(ISNA(VLOOKUP(B5;A$1:A$269;0))) : FALSE D5 :=A5=B5 :TRUE any ideas?

[Billef32]

@Firefly yes even though I changed it and even though I am getting TRUE there's still a problem. If I change a cell in column A then I should get a FALSE right? I don't get a false. :/ Thanks for your efforts. A5 :Adriana CCColman B5 :Adriana Colman C5 :=NOT(ISNA(VLOOKUP(B5;A$1:A$269;1;0))) : TRUE D5 :=A5=B5 :FALSE

[Jerry]

@Billef32 Can you perhaps provide us a picture of what you're getting? I'm quite confused as to why vlookup won't be working.

[Billef32]

it's working now.. it was some kind of bug I guess. the formula is right and firefly provided a correct answer :) Thank you all for your efforts :) have a nice day.

[Jerry]

@Billef32 That's great then :) Same to you. Oh and Firefly, you can make the formula a bit shorter if you use MATCH(). This will be equivalent: =ISNUMBER(MATCH(B1,A:A,0))