Using OR with JOIN COLLECT

Jesse Shockley

I'm struggling with the syntax on this one.

I have this formula that in its current state Joins and Collects if both Sales Lead and Sales Lead 2 match [Metric Grouping].

=JOIN(COLLECT({Master_Event and Year}, {Sales Lead}, [Metric Grouping]@row, {Master Event Info Sales Lead 2}, [Metric Grouping]@row), " | ")

However, I need it to Join and collect if {Sales Lead} matches [Metric Grouping] OR {Sales Lead 2} matches [Metric Grouping]

Thanks in advance

Paul Newcome

Because it is two different ranges, you would need to add a (hidden) helper column to the source sheet that will bring in one column or the other based on your criteria and then reference that in the JOIN/COLLECT.