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
