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


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!


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!