Formula to Populate MULTIPLE USABLE Contacts in a Single Cell
Alright... So there have been a lot of questions about using a formula to populate multiple usable contacts into a single cell, and it has always been said that it can't be done.
Well here is a solution that will start to fix that. I was presented with a challenge of populating multiple contacts into a single cell based on a multi-select dropdown column.
Reference Sheet:
Primary.....Contact
a................John Smith
b................Jane Doe
c................Paul Newcome
Working Sheet:
Multi-Select.........Output
a................................f
a b.............................f
a b c..........................f
Formula:
=JOIN(INDEX(COLLECT({Reference Sheet Contact Column}, {Reference Sheet Primary Column}, HAS([Multi-Select]@row, @cell)), 0), ", ")
Output:
Actually output the correct contacts as USABLE contacts into each of the single cells!!!!!! 🤯
Shout-outs:
@Susan Vieira for letting me highjack one of her posts to learn more about using 0 (zero) as the row number in an INDEX function.
@Genevieve P. For the brilliant idea of using 0 (zero) as the row number in an INDEX function.