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.

Find more posts tagged with

Formulas

Support

Accepted answers

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

All comments

Jakki Bruzzese

I would try using AND

Larry Ng

Thank you so much! This worked perfectly.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.