How to return multi-select values using INDEX/COLLECT


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

Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!