Show Multi-Contacts with a JOIN(COLLECT(...) formula -- working, but not showing as contacts


I have a Directory Sheet ('Sheet 2 -- Directory': with a list of Teams, Departments, and Contacts.

I want to pull the Contact(s) into another Sheet ('Sheet 1 -- Pull Into Sheet': where the Department matches (if there is no Department listed, it will default to the Team Contact).

I am using a JOIN(COLLECT(.. formula because the Department is a multi-select column since more than one department may be assigned a Task.

Here is my formula: IF(NOT(ISBLANK([Departments Involved]@row)), JOIN(COLLECT({Contact}, {Department}, HAS([Departments Involved]@row, @cell)), ", "), IF(ISBLANK([Departments Involved]@row), JOIN(COLLECT({Contact}, {Team}, HAS([Primary Team]@row, @cell)), ", ")))

It WORKS, but it does not bring the contacts in as contacts. They are brought in as text strings. The only way I can get it to work to bring in multi-contacts as contacts is to use an INDEX/MATCH formula, which I am not able to do with the 'Departments Involved' column being a multi-select column.

From my research, this isn't possible. Crossing my fingers someone has a solution or a suggestion on how to work around this.

If you want a copy of these 2 sheets to play around with, send me your email and I'll share a copy to you.

Thank you!



Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!