Best method to return a list based on a multi-select column
I hope I'm close with this solution, but I just can't make the formula work.
I have a list of therapists who are licensed in multiple states.
Sheet with formula: A list of patients needing a therapist. Their state is indicated in a "State" field.
Second sheet: A two-column range of "Therapist last name", and "States Licensed" (this column has a mulit-select of all US state abbreviations.
Example of a single record across the two columns of second sheet:
Smith FL, TX, CA
I want to run a search from the client sheet where I see the "State" and match it to every potential therapist in the second sheet, then return the names of those therapists.
My attempt is this, which is failing:
=JOIN(COLLECT({Therapist-LName}, {Therapist-State}, HAS({Therapist-State}, State@row,)) ", ")
Thanks in advance for any help.
Best Answer
-
Hi Sherri,
I believe this should work for you. Try inputting the formula below in a column named something like "Therapist Results" in your client sheet.
You could use a single select column "Client State" for the client sheet that also contains all of the state abbreviations.
The Therapist Results column should populate all of the names once a state is entered into the "Client State" column for that row.
=JOIN(COLLECT({Therapist-LName}, {Therapist-State}, CONTAINS([Client State]@row, @cell)), " , ")
Answers
-
Hi Sherri,
I believe this should work for you. Try inputting the formula below in a column named something like "Therapist Results" in your client sheet.
You could use a single select column "Client State" for the client sheet that also contains all of the state abbreviations.
The Therapist Results column should populate all of the names once a state is entered into the "Client State" column for that row.
=JOIN(COLLECT({Therapist-LName}, {Therapist-State}, CONTAINS([Client State]@row, @cell)), " , ")
-
Thank you so much, @Jeff M. ! This works perfectly!
-
Sherri,
No problem at all, glad I could help!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.5K Get Help
- 402 Global Discussions
- 213 Industry Talk
- 450 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 135 Just for fun
- 56 Community Job Board
- 454 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!