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

camp185

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.

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: