Trying to use JOIN, COLLECT, CONTAINS, in cross sheet formulas

lfontenot

I've reached a stalemate in my cross sheet formula and can't seem to get it to pull in all instances that I want it to.

I am attempting to look at two different sheets where either may contain the project number (TD Number), and have the corresponding BAR SME Engagement values returned.

=IF(HAS({WorkOrderID}, [TD Number]@row), JOIN(COLLECT({BLM BAR}, {WorkOrderID}, [TD Number]@row), ", "), IF(HAS({PL WorkOrderID}, [TD Number]@row), JOIN(COLLECT({PL BAR}, {PL WorkOrderID}, [TD Number]@row), ", ")))

I think I need to use CONTAINS, but have only been able to get HAS to work, but that only appears to work if the match is 1 to 1, as you can see in the bottom two rows. How can I make them populate and JOIN in the top row, which contains both of those project numbers?

Thank you in advance for any insight or advice!

Paul Newcome

You will need to use the CONTAINS function, but keep in mind that the CONTAINS function's syntax is opposite that of HAS.