IF Statement to return multiple values separated by commas
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.
Answers
-
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)),", ")
-
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.
-
Nevermind, I figured it out I was leaving the Char (10) out of my second formula
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 65.2K Get Help
- 445 Global Discussions
- 143 Industry Talk
- 476 Announcements
- 5K Ideas & Feature Requests
- 84 Brandfolder
- 150 Just for fun
- 71 Community Job Board
- 488 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 301 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!