Join-Collect from dropdown?

Lorraine Metler

I have a column named Territory, where I am trying to write a formula to populate it when names are chosen from an adjacent drop-down list column called Employee Attendee. If more than one name is selected from the Employee column, I would like their respective territories to populate in the Territory field, separated by a comma. I have a master sheet I can reference that has both the employee name and their associated territory. How would I write a formula to do so? I tried join collect, but it once an additional employee attendee is added, the formula doesn't work:

=JOIN(COLLECT({Master Sales and Marketing Employee List - Territo}, {Master Sales and Marketing Employee List - Full Na}, [Employee Attendee(s)]@row ))

Darren Mullen

@Lorraine Metler I assume all your references are correct. What you'll need to do is use has can kind of do a "reverse lookup" where you are matching the list in the lookup sheet to your multi select dropdown.

=JOIN(COLLECT({Master Sales and Marketing Employee List - Territo}, {Master Sales and Marketing Employee List - Full Na}, has([Employee Attendee(s)]@row,@cell) ))

Result:

Lookup sheet:

Above is the sheet with the formula applied.