Giving a 'Y' or 'N' depending on data existing in another column

[greatanaugi]

Hi all,

Hoping for a bit of help - got close to solving this one but can't seem to get over the line!

I'm trying to take data from one column and stating 'Yes' if it exists in another, and 'No' if not. So for example:


Column A has a list of numbers 1 - 400

Column B has a list of 200 numbers, ranging between 1 - 400 (for example 1,2,4,6,7,9,11....)

In Column C, I want a formula that states 'If the number in A1 also exists anywhere in Column B, then insert "Yes" into C1. If the number in A1 does not exist anywhere in Column B, then insert "No" into C1'.


Thanks for having a look!

[RobertMika]

The quickest
=MATCH(A1,B:B,0)
or as you requested:
=IF(ISNUMBER(MATCH(A1,B:B,0)),"Y","N")
drag down

[Olly]

Try:

Please Login or Register  to view this content.

[benishiryo]

welcome to the forum. another alternative:
=IF(COUNTIF(B:B,A1),"Y","N")

[greatanaugi]

Thanks all,

Got it working perfectly (ended up going down the ISERROR VLOOKUP route).

Cheers