Join Collect multiple columns from another sheet

I have this growing reference sheet with Site_Name and respective emails (1-3). Not all of them have all 3 emails. Please also assume that the emails are not beside each other. They could be 2-3 columns apart.

I need to merge their emails on this order tracker, but not sure how to use Join Collect if there are multiple columns.




  • ericncarr
    ericncarr ✭✭✭✭✭

    Not sure you will want to go this route, but the way I would personally do this is to list each Site/Email separately, so you would just have 2 columns, Site & Email.

    Then the join collect would work pretty easily:

    The formula would be straightforward enough too, just set your Company Email Merged to be a Multiple Select Dropdown column.

    =JOIN(COLLECT({Email column reference}, {Site Column reference}, =Site@row), CHAR(10))

  • heyjay
    heyjay ✭✭✭✭✭

    The sheet has already been established and we cant move or change the it since the three emails are designated to a level in the organization. thanks for trying though.


  • ericncarr
    ericncarr ✭✭✭✭✭

    @heyjay gotcha, worth a try.

    Noodled a bit, here's two ways to do it then but both will require helper columns. For any email columns you want to collect, create a helper column with the formula =[email 1], another with =[email 2] and so forth. This way you get the emails in a range next to each other. Once you have it set up, you can hide these columns.

    If you want it exactly like you have it above, the values separated by ";", then try this formula.

    =JOIN(COLLECT([Email 1]@row:[Email 3]@row, [Email 1]@row:[Email 3]@row, NOT(ISBLANK(@cell))), "; ")

    Of course, make the range the helper columns. This collects any values in that range that are not blank then joins them together with a "; " separating them.

    The other option is to do this, making the "Desired Result" column a multi-select dropdown. Just depends what you're doing with the data, if you're parsing it or something use the first option.

    =JOIN([Email 1]@row:[Email 3]@row, CHAR(10))

    Both options side by side

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!