Best method to return a list based on a multi-select column

SherriL

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.

Jeff M.

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)), " , ")

Jeff M.

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)), " , ")

SherriL

Thank you so much, @Jeff M. ! This works perfectly!

Jeff M.

Sherri,

No problem at all, glad I could help!