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

  • Jakki Bruzzese
    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")


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!