Can I use an IF formula to return a value where the reference column is a multi option drop down??

I am trying to create an IF or CONTAINS formula that references a multi option dropdown cell to returns an email address for every department selected in the cell.

i.e. 'Other Departments Impacted' CONTAINS "Sales" "Accounts" "Delivery" so the 'Associated Contacts' cell returns "John Citizen", "Fred Smith", "Tony Dory".

Is this possible?


Best Answer

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓


    Have you thought about using Automation to Assign People?

    The tricky part is that you can't have any subsequent actions after Assign People in the same automation. But, depending on how many different departments you have, you could make a condition path for each possible combination of departments that might be listed in the Other Departments Impacted (ODI) column.

    Condition path 1: If ODI has Sales, Assign John Citizen

    Condition path 2: If ODI has Accounts, Assign Fred Smith

    Condition path 3: If ODI has Delivery, Assign Tony Dory

    Condition path 4: If ODI has Sales and Accounts, Assign John and Fred

    Condition path 5: If ODI has Sales and Delivery, Assign John and Tony

    Condition path 6: If ODI has Sales, Accounts, Delivery, Assign John, Fred, and Tony

    Condition path 7: If ODI has Accounts and Delivery, Assign Fred and Tony


    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!