How to compar two fields that have multiple options, any match = true

Example of my issue, Looking for match, from these two multi select fields.

Field A, options are apple, orange, lemon, lime

Field B, options are apple, orange, lemon, lime

Field C. If match found = 1

User selects options apple, orange, lemon from Field A.

User selects options apple, lemon from Field B.

I want Field C to indicate true since apple, and/or lemon existed in both fields...basically there was at least one match, so good enough.

I've tried HAS and CONTAIN, but not having much luck. The formula is basically looking for the value of A "apple orange lemon" within B.

Find more posts tagged with

Accepted answers

All comments

Mike TV

This should work.

Example sheet:

Formula (make sure it is a checkbox column type):

=IF(AND(HAS([Fruits A]@row, "Apple"), HAS([Fruits B]@row, "Apple")), true, IF(AND(HAS([Fruits A]@row, "Orange"), HAS([Fruits B]@row, "Apple")), true, IF(AND(HAS([Fruits A]@row, "Lemon"), HAS([Fruits B]@row, "Lemon")), true, IF(AND(HAS([Fruits A]@row, "Lime"), HAS([Fruits B]@row, "Lime")), true, ""))))

camp185

I think that works if (like in your example Fruit B) has only one selection. Thank you for the response though.

This is what I ended up doing:

=IF(OR(AND(HAS([Field A]@row, "Apple"), HAS([Field B]@row, "Apple")),

AND(HAS([Field A]@row, "Orange"), HAS([Field B]@row, "Orange")),

------and so on for every field-----

), 1, 0)

Mike TV

@camp185

There are definitely different ways you can write formulas to end up with the same results. I still get "true" when I should no matter how many items I select or dont, so my formula is definitely working:

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.