JOIN(COLLECT 2 CONTAINS criteria with multiple returns Cross Sheet

Patsy Peterson

Hello!

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!

Bassam Khalil

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)),":")

Bassam Khalil

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)),":")

Patsy Peterson

Hi @Bassam Khalil

Almost! It only returns one result, but at least it returns something.

Bassam Khalil

@Patsy Peterson

If you like share me as an admin on a copy of your sheet after removing any sensitive data and I will check it for you.

Patsy Peterson

@Bassam Khalil

I appreciate the offer, but we have it locked down to only our own domain.

Patsy Peterson

@Bassam Khalil This actually did work, it was user error causing the malfunction.

Once I changed the ":" to "; ", the wrap text displayed all the results.

Thank you so much for your help!

Bassam Khalil

@Patsy Peterson

Excellent, I will be happy to help you any time.