Return multiple contacts based on department dropdown list.

BrittneyAnn

Hello,

I would like to use a master list with a list of people attached to the department that populates multiple contacts in the same cell depending on what dropdown departments are selected.

So Sheet 1:

Department Email

Marketing Billy@help.com

Operations Bob@help.com

Sheet 2:

Departments (dropdown multi select) Autopopulated info from sheet 1

Marketing Operations Billy@help.com, Bob@help.com

I am able to vlookup if only one department is selected but once multiple are selected I get an error. Does anyone know the appropriate formula to return emails for all departments selected? There are around 34 different department types

jmyzk_cloudsmart_jp

Hi @BrittneyAnn

Unfortunately, creating multiple contacts with the formula is not possible now.

https://community.smartsheet.com/discussion/87741/contact-field-listing-multiple-contacts-to-feed-individual-rows-in-a-report

So, a workaround is to create multiple contact columns like the demo sheet below.

https://app.smartsheet.com/b/publish?EQBCT=c4e9e12c520649eab9a90c85f93f7926

Then, you can use them in workflow automation, for example.

The formulas to create multiple single contacts are as follows;

[Contacts] =JOIN(COLLECT({Master List : Email}, {Master List : Department}, CONTAINS(@cell, Departments@row)), ",") —> This formula just create text outputs, not contacts.
[Count] =COUNT(COLLECT({Master List : Email}, {Master List : Department}, CONTAINS(@cell, Departments@row)))
[Contact 1] =IF(Count@row >= 1, INDEX(COLLECT({Master List : Email}, {Master List : Department}, CONTAINS(@cell, Departments@row)), 1))
[Contact 2] =IF(Count@row >= 2, INDEX(COLLECT({Master List : Email}, {Master List : Department}, CONTAINS(@cell, Departments@row)), 2))
[Contact 3] =IF(Count@row >= 3, INDEX(COLLECT({Master List : Email}, {Master List : Department}, CONTAINS(@cell, Departments@row)), 3)) …..

By the way, if you use Smartsheet API, creating multiple contacts from emails is possible. Below is a video I posted somewhere in the community to demonstrate that.

https://youtu.be/IkX2EqTFwlY