How can I look up in multi select dropdown list?
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”
How can I look them up (in the User Management sheet)?
Kind regards
Stéphane
Answers
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!