Help! Multiselect Column Formula with INDEX, MATCH and HAS

Alexis Lee

Hello Smartsheet World!

I am attempting to run a formula that will return contact emails depending on which departments are selected in a multi-select dropdown column.

Sheet 1: Improvement Notes Smartsheet

-Dept Attention column is a multi-select dropdown

-Department Contacts column (in green) is a contact list that allows multiple contacts (this is where I want to add a formula that auto-populates the contacts based on the departments selected in the Dept Attention column)

Sheet 2: Department Contact Guide Smartsheet

- Smartsheet that the formula would reference to lookup which contacts are associated with each department

From my research, I've seen a combination of formulas but am stumped on what direction to take:

=IF(NOT(ISBLANK([Department Attention@row])), INDEX(COLLECT({Department Contact Guide Contacts }, {Department Guide Dept Attention}, HAS(@cell, [Department Attention@row]), 1), " ")

The formula needs to be fixed and I'm unsure if I'm doing this right or if what I'm attempting to accomplish is possible.

Any guidance is greatly appreciated!

-Alexis

Paul Newcome

There is currently no way to use a formula to output multiple usable emails into a single cell (at least not easily).

You would have to create a table that has every possible combination accounted for, then you can INDEX/MATCH. But you cannot use a JOIN function to pull in both the IT people and the HR people from two different cells into one and have them as usable contacts.

You can pull them in, but it would be stored as a text string and really informational only (can't be used in automations and whatnot).

Paul Newcome

Happy to help. 👍️

Paul Newcome

There is currently no way to use a formula to output multiple usable emails into a single cell (at least not easily).

You would have to create a table that has every possible combination accounted for, then you can INDEX/MATCH. But you cannot use a JOIN function to pull in both the IT people and the HR people from two different cells into one and have them as usable contacts.

You can pull them in, but it would be stored as a text string and really informational only (can't be used in automations and whatnot).

Alexis Lee

Thank you Paul, this is very helpful!

Paul Newcome

Happy to help. 👍️