IF Statement to return multiple values separated by commas

JCluff

If any option is selected in the below example, what formula can I use to provide multiple results separated by a comma in a single cell?

Examples:

Option 1 & Option 3 selected, Formula returns "Form 1, Form 3"

Option 1, 4 & 5 are selected, Formula returns, "Form 1, Form 4, Form 5"

Also, is there an alternative way to reference each value in the drop down other than enclosing the text in quotations? When I implement the formula the "options" will be longer strings of text.

Leibel S

@JCluff

you can do it by having a separate sheet with your list of options in 1 column and the value you want to return in the other.

Your formula would then be the below (set name of cross sheet references as needed)

=JOIN(COLLECT({return value column},{option list column},HAS([Question 2 Description]@row, @cell)),", ")

Hollie Green

I have run into an issue with this formula. I am not utilizing the has portion. It worked perfectly with the Join function alone in row 1 where the 7022114 and 7052779 is located. When I add in the Collect portion it combines some of the multi select into one entry but I can't find anything in the data entry to cause this and it doesn't join all the entries in the cell just some of them. Any thoughts? the formula that is a 42 and a 37 is a distinct value formula so it is causing it to return incorrect count when I add the condition with the collect.

Hollie Green

Nevermind, I figured it out I was leaving the Char (10) out of my second formula