JOIN/COLLECT when referencing a Dropdown (Multi Select)


I am trying to use a conditional IF based JOIN/COLLECT to generate a phrase in another column for notification purposes, but the column that the JOIN/COLLECT is referencing is a multi-select column and I can’t figure out a way to join multiple items from that column together.

The column of interest is a list of possible allergens that could be in a food product. Based on the number of allergens present the phrasing may change. For instance. If “wheat” is chosen as an allergen It would say “Contains: wheat”, If “wheat and milk (lactose)” are chosen as allergens it would say “Contains: wheat and lactose” and then a final option if more than two are chosen the final result would include commas in addition to the “and”. I am trying to avoid including the allergen selections in separate columns, but currently if I try to JOIN/COLLECT 2 or more selections on the multi drop down it repeats both selections. Example: “Contains: Milk (Lactose) Wheat and Milk (Lactose) Wheat”

Below are screenshots of the columns and formula currently being trialed.

Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!