Compare two columns and return a yes or no

BerryC

I have two columns on one sheet that I want to compare. If the value in Column A matches any value in Column B, I want a yes to be returned in Column C. I've played with the formula for over an hour and can't get it right.

=IF(CONTAINS([RDS Serial #]@row, [Serial Number]:[Serial Number]), "YES", "NO")

Kelly Moore

Hey @BerryC

Here's an alternative approach

=IF([RDS Serial #]@row <> "", IF(COUNTIFS([Serial Number]:[Serial Number], [RDS Serial #]@row, [Serial Number]:[Serial Number], <>"") > 0, "Yes", "No"))

Will this work for you?
Kelly

jessica.smith

The UNPARSEABLE error might just be a minor syntax issue. I input the HAS formula in a sheet with your example data and it seems to work fine.

jessica.smith

Hey @BerryC

Try the HAS function instead

=IF(HAS([Serial Number]:[Serial Number], [RDS Serial #]@row), "YES", "NO")

Does this work for you?

BerryC

I get an UNPARSEABLE error

Kelly Moore

Hey @BerryC

Here's an alternative approach

=IF([RDS Serial #]@row <> "", IF(COUNTIFS([Serial Number]:[Serial Number], [RDS Serial #]@row, [Serial Number]:[Serial Number], <>"") > 0, "Yes", "No"))

Will this work for you?
Kelly

rrenee

Could you tell us what error you are getting specifically? The formula is correct, so I do not think that is the issue.

I remade it and it worked using the exact same format so it may be another cause:

BerryC

Hi Kelly,

It kind of works. My two columns will not have the values directly across from each other. When the two columns are populated, I get a yes, but when the columns don't make the no value is not returned.

BerryC

When I run the formula provided by Kelly, it works perfectly when the RDS Serial# and Serial Number match; I get a Yes in the box next to the column with the RDS Serial # populated. However, if the RDS Serial # is not populated, I get no Return.

jessica.smith

The UNPARSEABLE error might just be a minor syntax issue. I input the HAS formula in a sheet with your example data and it seems to work fine.

Kelly Moore

@BerryC

Try this

=IF(COUNTIFS([Serial Number]:[Serial Number], [RDS Serial #]@row, [Serial Number]:[Serial Number], <>"") > 0, "Yes", "No")

Kelly