Formula to show if a single contact list entry exists in a different multiple contact list entry

Options

Long time fan, first time community poster, but I have a formula help request.

See the screenshot of what I am trying to accomplish.

Basically I have three columns.

Column 1 = Single Contact list that will always have a contact (In this example we call them "Person A")

Column 2 = Multiple Contact list that will have 2-4 names in it (In this example we call them "Person A" and "Person B"

Helper Formula Column = I need a true/false formula to know if Contact A exists in Column 2

I have tried variants of =IF(Contains... and =IF(Has(... and have not got it working correctly.

This should be easy...right?


If you are curious, it will be used for the automated proofing columns. We use PRIMARY approvers as the final word on proofs and we can't call something "Approved" unless they give their word. We often have SECONDARY approvers on proofs who may not even state their opinion. I'd want just once a PRIMARY approver says its approved, then we get a column stating so (that is not how the automated proof status columns work).

Answers

  • BullandKhmer
    BullandKhmer ✭✭✭✭✭
    Options

    =IF(CONTAINS([Single Assignment]@row,[Multiple Assignment]@row),"True","False")

  • Robert Tunick
    Options

    Thank you @BullandKhmer , that is the first thing I tried as well. The logic works, but it does not work in practice.


    As I mentioned, I tried =IF(Contains... and =IF(Has(... and have not gotten either working correctly.

    Any other leads to new ideas that may work?

    Thank you all SO much.


    Here is a screenshot of it not working.


  • Robert Tunick
    Options

    Hmm

    =IF(HAS([Multiple Assignment]@row, [Single Assignment]@row), "True", "False")

    seems to work. It didn't work when I tried it on the real sheet, but in my demo dummy sheet it is working.

    Hmmmm

  • Robert Tunick
    Options

    Ok, here is where it gets broken. It seems flast@email.com different than the same email address that is verified in their contact name.

    Small nuances could cause issues...have to test and think about this more.

  • BullandKhmer
    BullandKhmer ✭✭✭✭✭
    Options

    =IF(CONTAINS([Single Assignment]@row,[Multiple Assignment]@row),"True","False")


    This should work.


    um... if its not... as you say... I'd start by confirming you have selected the same contact with the same email and name in both columns... but it looks like you have, so...

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!