VLOOKUP/JOIN/COLLECT - Need help combining groups of emails

Ashley Pierce

Good afternoon,

I am stumped & hoping someone can help. In short, I have a Reference Sheet with the contact info and a Communication Plan Sheet. In the Reference Sheet, I added an Address/Filter/Email List column. The Email List is grouping all of the addresses based on which group they are in, so when you copy the Email List and paste it in outlook, it can email everyone in that group. The problem; however, is that in the Communication Plan, whenever there is more than 1 group or Audience, the formula I wrote below will not combine the Email Lists into one cell.

What I need help with is a formula that will reference the Shared Services Group Reference sheet's Email List based on the Filter, and then will combine the Email Lists based on the groups/audiences chosen in the Communication Plan sheet under the column "Emails". Thank you in advance!

Paul Newcome

Try a JOIN/COLLECT instead.

=JOIN(COLLECT({Other Sheet Email Column}, {Other Sheet Group Column}, HAS(Audience@row, @cell)), "; ")

Paul Newcome

Try a JOIN/COLLECT instead.

=JOIN(COLLECT({Other Sheet Email Column}, {Other Sheet Group Column}, HAS(Audience@row, @cell)), "; ")

Ashley Pierce

You're my hero Paul! Thank you so much!!!

Paul Newcome

Happy to help. 👍️