Index Collect and Multiple Output Values

KDev

Hi All,

I have multiple things going on and am hoping I can find one formula to clean things up for my team. Basically, I have the below list as a helper sheet.

Then, i have a form that our customers can complete. They can select from the list that is on the left of the example below and they can select multiple options (its a dropdown list on the form) with those values.

For our team's purposes, we don't need all of that description so I want a new column on my form sheet to only provide a list of the codes as per the column on the right on my helper sheet. Again, the resulting cell could have multiple selections. So as an example, "A4-Starting Small: Small-Town Singles (<35 years)" and "C13-Retiring on Empty: Singles (55-64)" so for our internal column in the form, I would only want "A4, C13" to come out.

So, the formula needs to do two things, one look at what the customer asked for (so the long selections on the form) and then look at my helper sheet and determine what the simplified name is (like A4) and add it to the column I created.

Is this even possible?

Jason Tarpinian

I don't think this is possible. JOIN(COLLECT()) is ideally what you'd like to use, but looking up multiple values in a cell across a range is not possible.

The only workaround I can think of would look something like:

Where you have an individual HAS() function for each possible option that either adds your 2letter code or not. You'd have to update the formula each time a new option becomes available, which depending on how often that happens, maybe it's not so bad?

Paul Newcome

You can use a JOIN/COLLECT.

=JOIN(COLLECT({Reference Sheet Internal Column}, {Reference Sheet Suppliers Column}, HAS([Multi-Select Column Name]@row, @cell)), "delimiter of choice")

Jason Tarpinian

That is beautiful @Paul Newcome! I was so close but couldn't get the criteria to return the individual pieces. Keeping that one in my formula vault for later!

KDev

Thank you @Jason Tarpinian and @Paul Newcome. I have not used this function before and am not that well versed with formulas in general. So, where you have "Multi-select column name" you mean the column within the form that suppliers choose from? Also, what do you mean by delimiter of choice? Are you saying I basically would need to list out all the options for the result? Like "A1, A2.." etc.? The column that needs the result is called "Cohort Groups - Version 1 - Internal". So:

Helper Sheet columns (the Helper Sheet is called "Helper Sheet for Cohorts")

"Cohorts Suppliers"

"Cohorts Internal"

Form Columns:

Cohort Groups - Version 1 (results selected in form, same as Cohorts Suppliers but in a drop down)

Cohort Groups - Version 1 - Internal (where I need the "cohorts internal" to populate)

Could you assist me with writing it out? I really appreciate it.

Jason Tarpinian

The "Multi-select column name" field would be the form field that the suppliers could choose the multiple options.

The delimiter is just how you want to concatenate the mulitple values. So if you want to use a comma, you would put ", " into the formula, then all your joined values will be ABC, DEF, HIJ. Or if you deliminate with " - " for ABC - DEF - HIJ.

So if I'm following your naming convention, it might look like this (your cross-sheet ranges would obviously be different names, easier to test on a single sheet for me)

=JOIN(COLLECT([Cohort Groups - Version 1 - Internal]:[Cohort Groups - Version 1 - Internal], [Cohort Groups - Version 1]:[Cohort Groups - Version 1], HAS([Cohorts Suppliers]@row, @cell)), ", ")