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

Options

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.

Answers

  • Mike TV
    Mike TV ✭✭✭✭✭✭
    Options

    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
    Options

    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
    Mike TV ✭✭✭✭✭✭
    Options

    @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:


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!