Ignore blanks while detecting matching text across multiple columns
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.
Best Answer

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")
Answers

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")

Thank you so much! This worked perfectly.
Help Article Resources
Categories
Check out the Formula Handbook template!