How can I look up in multi select dropdown list?

Stephane

I have a sheet that contains the columns

-         Name (type = multi select dropdown)

-         Email (type = Contact list with “allow multiple contacts per cell)

We manage the email addresses in a User Management sheet. This sheet contains the columns:

-         Name (type = text/number)

-         Email (type = text/number)

 

In the example in attachment, I have 2 names in the Name column. I need to automatically add the email addresses of these 2 persons in the column “Email”

Example.png

How can I look them up (in the User Management sheet)?

Kind regards

Stéphane

Genevieve P.

Hi Stéphane,

We don't currently have a specific function that can look at a multi-select dropdown like this and return all of the different values into a multi-select contact column from another sheet that relate to each selection. Please submit an Enhancement Request when you have a moment!

Depending on how many names and emails you have, you could build out your User Management sheet to have all possible combinations. You will also want the Email column in this User Management sheet to be a Multi-Select Contact column, with the emails selected.

Then in your other sheet you can do a VLOOKUP formula to return the contacts in the Email column if the Name column matches in both sheets:

=VLOOKUP(Name@row, {Names & Emails}, 2, 0)

If you could explain your process, there may be a different way to set up your sheets to achieve the same result. For example, are the emails in the Contact list of your initial sheet used for Automations/Alerts, etc? (Do they need to be a Contact type of email) Or are they just collected for data purposes?

In addition, is there a reason why the Names column is multi-select? Could you instead use multiple columns (such as "Contact 1" and then "Email 1", "Contact 2", "Email 2", etc). It would be helpful to see screen captures of your sheets, without any sensitive data, as well as an explanation of how this will be used.

Let me know if you have any questions about this!

Thanks,

Genevieve