Multi option & contact pull

NealT

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.

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 :)