Struggling with Matching/Referencing values from a Multi-Select


I have a Sheet labeled Resources with a single-select column containing a list of users/contacts. The sheet also contains a column titled Hours. I have another Sheet labeled Project with a multi-select column where one or more of the same users/contacts may be selected. I have a target column on this Project sheet where I would like to pull a list of those hours where the contact matches. In truth, I'd actually like to get to the max hours, but am working on getting the list of hours first and running into challenges. Here is the formula I've been trying to work with:

=JOIN(COLLECT({Resources_Hours}, {Resources_Users}, HAS(@cell, [Contacts]@row)), " | ")

This formula is returning the right result when the multi-select "Contacts" column on the Project sheet has one selected contact, but returning blank when it has multiple. I can't seem to figure out why this isn't working. Any help would be appreciated. Thanks!


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!