JOIN(COLLECT 2 CONTAINS criteria with multiple returns Cross Sheet



I'm looking to build a JOIN(COLLECT that will allow me to pull email addresses from a contact sheet based on two criteria. Both criteria have to be CONTAINS. They will be the Department (Dept) and the Job Title (Role).

I frequently send out emails based on department, but also specifically to the managers of those departments (which change with some frequency). Also to all managers, regardless of department.

Ideally, I would like to be able to input the Department name, as well as the Role group, to narrow down to only the email addressed I need.

Below are the two formulas I have that work. I would like to combine them so that I have a role within a department option.

=JOIN(COLLECT({Contact Email2}, {Cost Code}, CONTAINS(Dept@row, @cell)), "; ")

=JOIN(COLLECT({Contact Email2}, {Job Title2}, CONTAINS(Role@row, @cell)), "; ")

Thank you!

Best Answer

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    Answer ✓

    Hi @Patsy Peterson

    Hope you are fine, please try the following formula:

    =JOIN(COLLECT({Contact Email2}, {Cost Code}, CONTAINS(Dept@row, @cell), {Job Title2}, CONTAINS(Role@row, @cell)),":")

    PMP Certified

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!