Multi option & contact pull

I have a form setup so that users are able to select multiple countries from a drop down list. This then shows correctly on my sheet as intended. Assocatied with these countries there is a contact as well so need to link the two together so that when a country from the list is selected then the right contact name/email address is also populated in another cell.

E.g. Helper sheet

Region or country ¦ contact name ¦ contact email address

Greece ¦ Contact name 1 ¦ Contact email address 1

Italy ¦ Contac name 2 ¦ Contact email address 2

France ¦ Contact name 3 ¦ Contact email address 3

Intake sheet e.g.

Region / Country ¦ Contact details

Greece , Italy ¦ Contact email address 1, Contact email address 2

I am looking to find a formula that would lookup the region/country from the region /country field on the intake sheet and then give all the email addresses to those countries in the contact details field.

Find more posts tagged with

Accepted answers

All comments

bisaacs

Hey @NealT,

I worked on this for a little bit, and honestly the hardest part I'm struggling with is collecting multiple values based on the multi-dropdown list. I can collect the name/email for single country selections, but as soon as multiple countries were selected in the dropdown my formula stopped working.

I did some searching around the forums and found a similar question being asked about a year ago, and it appears there isn't a solution for this (unless something has changed in the last year):

https://community.smartsheet.com/discussion/106906/assign-multiple-people-to-a-row-based-on-multi-select-drop-down

Sorry I couldn't be of further help!

NealT

@bisaacs Thanks for sending over that link. Much appreciated. I have posted on there with regards to the workaround for further advise.

I initially had it as a single selection but then was requested to build it out so that multiple options could be selected. It was nice and simple using just a plain old Vlookup when it was a single entry. Now thats its a multi option I have searched high and low to see if there is any sort of solution and came up with 0 so far. If it can output multiple entries into a cell then there must be some sort of coding that defines those which can be used in referencing in a formula? Maybe something a smartsheets dev can weigh in on?

bisaacs

Hey @NealT,

How many countries (at most) could someone select? Could you maybe provide "Country/Region 1", "Country/Region 2", etc, then have corresponding Contact columns for each region option? Then you could still use the VLOOKUP formula as well as still allow for multiple selections.

NealT

Hi @bisaacs

I was also thinking about doing this as an option and have multiple fileds with the drop down lists. I will confirm with the lead if they are happy to go this route and change the design as it will make life alot easier to then just do a vlookup and then also for KPI metrics. Maybe one day Smartsheets might be able to figure something out on how to use the data in a multiple value field :)

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.