Comparing one value to multiple values

Hello all,

I have two sets of values in two columns.

For example, in column A, I have 200 different values, and in column B, I have 150 different values

I am trying to write a formula that checks each value in column B and compares it to ALL values in column A to see if there is a duplicate. I will run this formula against all values in column B which checks each individual B value against all the values in A.

Could someone help me out with this? The only way I could think to do it is:

=IF(B1=OR(A1:A200),"Yes","No")

I am getting a #VALUE error on this one, so I assume the OR statement won't check individually each value from A1 to A200.. Or am I completely backwards?

Thanks for any help that can be provided!

You could use OR but the syntax is slightly different

=IF(OR(B1=A$1:A$200),"Yes","No")

....and that's an "array formula" so needs to be confirmed with CTRL+SHIFT+ENTER

Two more common non-array approaches though, either

=IF(ISNUMBER(MATCH(B1,A$1:A$200,0)),"Yes","No")

or

=IF(COUNTIF(A$1:A$200,B1),"Yes","No")

That's great, the =IF(COUNTIF(A$1:A$200,B1),"Yes","No") foruma worked flawlessly! Thanks for your help.