Extract values from a dropdown list with multiple selections and place each value into its own cell

Copetope

Hello

Hoping someone can help a newbie with trying to figure out how I can go about taking selections from a drop box selection with multiple values and placing them into their own cell, to allow for a VLOOKUP of each value to pull information from another sheet. the VLOOKUP and I can figure out once I get each value into its own cell.

Example:

I have selected 2 store numbers from the drop downlist, 1185 and 1188. I can see these values in the cell on my sheet. I want to extract those values and place them into another sheet, each on their row, to allow for a vlookup to reference another sheet and populate the required information for each store I selected. The formula to extract and place each value into their own cell will need to be dynamic as I may need to place 2 stores, 20 stores or 50 stores.

Can anyone help with this?

Genevieve P.

Hey @Copetope

There currently isn't a way to extract individual selections from a multi-select cell and parse them into multiple, separate cells.

Please feel free to add your vote and voice to this Product Idea when you have a minute: Add function to retrieve values from cell for a multi-select column

That said, if you already have a reference sheet somewhere with your Store information on individual rows, you could use a JOIN(COLLECT formula to return data from that second sheet into your current sheet with the multi-select.

=JOIN(COLLECT({Column to Return}, {Single Select Column}, HAS(Multi@row, @cell)), ", ")

Cheers,

Genevieve

PeggyLang

@Genevieve P.

I am looking to do something very similar. I have a list of technicians each of whom look after a set of stores (ie 4 or 5 different stores). I pull together a ‘after hours on call’ sheet whereby I may assign ‘John Smith’ to be on call for his store set, Joe’s store set and Mark’s store set.
my hope was to have a multi select cell where I could enter John, Joe and Mark and the cell beside it would populate with all three of their store sets into one multi select cell. I need the cell with all store #’s to be multi select as I have another sheet that employs a formula containing ‘Has’ so need to make sure I am able to identify any one of the individual stores.
Thoughts?

Genevieve P.

Hi @PeggyLang

Did you try the formula structure above? The one tweak you may need to make is to have the separator be CHAR(10) instead of a comma so that it parses out the values into multi-select values.

=JOIN(COLLECT({Column with Stores}, {Single Select Name Column}, HAS(Multi@row, @cell)), CHAR(10))

Cheers,
Genevieve

PeggyLang

@Genevieve P.
BINGO!!!! It worked and I can't even quantify the time savings to our technicians this will result in.
THANK YOU!!!!
Happy 4th of July!!!

Genevieve P.

Amazing! I'm glad to hear this helped 🙂