How to return multi-select values using INDEX/COLLECT

Elguapo24

Need the Smartsheet fam expert knowledge. I am trying to return a value (persons name) based on the hospital dept they work in. I created a reference guide crosswalk with name in one column and the name of the dept in another. There are multiple names that work in the same dept FYI. I import hospital data and am trying to create a formula in a new column to assign all nurses to the dept using the crosswalk.

The crosswalk only has two columns DEPT NAME and RN EDUCATOR

I am using the DEPT ID in the source import sheet I am trying to reference

I created a vlookup but that only returns one value. I have read that I should use the INDEX/COLLECT but keep getting errors. This was my attempt below

=INDEX(COLLECT({educator range}, {Range}, [Combine:BU/Dept]@row), 2, 2)

[educator range} is the RN educator name column in my crosswalk

{range} is both the dept name and RN in my crosswalk

[Combine:BU/Dept]@row references the dept in my data source sheet

Paul Newcome

You are actually going to need a JOIN/COLLECT to bring together multiple cells from the reference sheet to the working sheet.

=JOIN(COLLECT({educator range}, {department}, [Combine:BU/Dept]@row), "delimiter of choice")

Paul Newcome

You are actually going to need a JOIN/COLLECT to bring together multiple cells from the reference sheet to the working sheet.

=JOIN(COLLECT({educator range}, {department}, [Combine:BU/Dept]@row), "delimiter of choice")

Elguapo24

That was perfect @Paul Newcome ! Thanks so much really appreciate it.

Paul Newcome

Happy to help. 👍️