Formula help needed: multiple combinations
Hello,
I am in need of help with a formula. Basically, I have a formula looking at column A. Column A has 9 dropdown options, multiselect. Each of the 9 options has a specific (i.e., different) email associated with them.
I have a large IF function that will accurately give the appropriate email, based on a single dropdown choice. BUT, in the event that more than one of the 9 dropdown options are selected, I need to be able to show all corresponding emails. Would this be a CONTAINS? AND?
Answers

To handle multiple selected options and display all corresponding emails in Smartsheet, you can use a combination of the IF, CONTAINS, and JOIN functions. Let's assume the 9 dropdown options are in column A, and you want to display the corresponding emails in column B.
Here's the formula you can use in cell B2:
=IF(CONTAINS(A2, "Option1"), "email1@example.com", "")
& IF(CONTAINS(A2, "Option2"), ", email2@example.com", "")
& IF(CONTAINS(A2, "Option3"), ", email3@example.com", "")
& IF(CONTAINS(A2, "Option4"), ", email4@example.com", "")
& IF(CONTAINS(A2, "Option5"), ", email5@example.com", "")
& IF(CONTAINS(A2, "Option6"), ", email6@example.com", "")
& IF(CONTAINS(A2, "Option7"), ", email7@example.com", "")
& IF(CONTAINS(A2, "Option8"), ", email8@example.com", "")
& IF(CONTAINS(A2, "Option9"), ", email9@example.com", "")
Explanation:
 The formula uses the IF function along with the CONTAINS function to check if each option is selected in column A (cell A2).
 If an option is selected, it adds the corresponding email address to the result. If not, it adds an empty string ("") to the result.
 The JOIN function concatenates all the email addresses together, and since the email addresses are separated by commas, it will display them as a commaseparated list.
Note: The formula assumes that only one cell in column A will be selected with multiple options. If multiple cells in column A may have multiple selections, you might need to use an ARRAYFORMULA in combination with the TRANSPOSE function to handle multiple rows. However, ARRAYFORMULA is not natively supported in Smartsheet, so you might need to consider alternative approaches or use additional formulas depending on your specific use case.

Thank you so much for the speedy response, Marcia!
I was able to use the formula you provided, but I am still only seeing one email address. I think I may need the array formula option... I will come up with a workaround to avoid a more complex formula set.
Thanks again!

Is this string of emails for display only or for sending out automations? If it is a string only, you would write out separate IF/HAS formulas (one for each option) and then "add" them together.
=IF(HAS([Dropdown Column]@row, "Option 1"), "Email 1", "") + IF(HAS([Dropdown Column]@row, "Option 2"), "Email 2", "") + ...............
and you will need to get a bit creative with your delimiter.
If it is for notifications, unfortunately it isn't possible. We can use formulas to create a text string of email addresses, but we cannot use a formula to output multiple USABLE contacts into a single cell.

Thank you, Paul  it would be for automations/notifications. Would be nice to be able to do this!

I believe there is a "Product Idea" (at the top of this page) already submitted if you wanted to add your vote. I can't seem to find it at the moment for some reason, but I do know it is out there somewhere.
Help Article Resources
Categories
Check out the Formula Handbook template!