Formula help needed: multiple combinations

jl.furstenberg

Hello,

I am in need of help with a formula. Basically, I have a formula looking at column A. Column A has 9 drop-down options, multi-select. 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 drop-down choice. BUT, in the event that more than one of the 9 drop-down options are selected, I need to be able to show all corresponding emails. Would this be a CONTAINS? AND?

Marcia D

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 drop-down 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 comma-separated 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.

jl.furstenberg

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!

Paul Newcome

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.

jl.furstenberg

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

Paul Newcome

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.

Martin_M

https://community.smartsheet.com/discussion/comment/388087#Comment_388087

Hi,

I've used the suggested formula as well but received a non parseable error so I tried the following:

instead of:
=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", "")
…

try:
=IF(CONTAINS("Option1", [A2]@row), "email1@example.com", "")
+ IF(CONTAINS("Option 2", [A2]@row), ", email2@example.com", "")
+ IF(CONTAINS("Option 3", [A2]@row), ", email3@example.com", "")
+ IF(CONTAINS("Option 4", [A2]@row), ", email4@example.com", "")
…

Basically place the dropdown option before your column and add @row.
Instead of the & symbol use a +

Hope this works, but you might have found a different solution since you originally posted your question.