Ignore blanks while detecting matching text across multiple columns

Larry Ng

Hello! I am trying to write a formula for detecting ANY matches between "Affiliation" and "Affiliation 1," "Affiliation 2," or "Affiliation 3". If any of them match "Affiliation", then "conflict" is true. If false, it should say "none".

However, if "Affiliation" is blank and matches another blank, it also outputs "conflict".

How do I exclude the blanks? I think I need to use ISBLANK but cannot figure out how to integrate it.

Jakki Bruzzese

I would try using AND

=IF(AND(NOT(ISBLANK(Affiliation@row)), OR(Affiliation@row = [Affiliation 1]@row, Affiliation@row = [Affiliation 2]@row, Affiliation@row = [Affiliation 3]@row, Affiliation@row = [Affiliation 4]@row, Affiliation@row = [Affiliation 5]@row)), "conflict", "none")

Jakki Bruzzese

I would try using AND

=IF(AND(NOT(ISBLANK(Affiliation@row)), OR(Affiliation@row = [Affiliation 1]@row, Affiliation@row = [Affiliation 2]@row, Affiliation@row = [Affiliation 3]@row, Affiliation@row = [Affiliation 4]@row, Affiliation@row = [Affiliation 5]@row)), "conflict", "none")

Larry Ng

Thank you so much! This worked perfectly.