Struggling with Matching/Referencing values from a Multi-Select

Vince K

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!

Paul Newcome

Try flipping the arguments within the HAS function.

HAS(Contacts@row, @cell)

Vince K

Thanks for the response! Unfortunately, flipping it seems to give an Invalid Operation error.

Vince K

Thanks for the response! Unfortunately, flipping it seems to give an Invalid Operation error.

Paul Newcome

Are you abel to provide a screenshot of the formula open in the sheet as if you are about to edit it?

Vince K

Here's the original formula:

Here it is with the HAS function inverted (throws the error):

Update: I'm not sure what happened, but I just inverted it again today to take that screenshot above and it started working! I have no idea why it wasn't working yesterday, everything is the same, including the data. Maybe I didn't have a space between the comma and the @?

Paul Newcome

The space shouldn't have made a difference, but it could have been some old data stored on the back-end. I have had that happen to me before as well. Glad it is working for you now.

Vince K

@Paul Newcome, can you share some detail on the "old data stored on the back-end" and how to fix that in a sheet? I ask because, all of a sudden the formula stopped behaving properly. Another user updated and reordered the reference sheet but all the proper data is there. The sheet making the call however, is omitting some of the values / not finding them. The same formula works fine in another (dummy) sheet. This is causing some major challenges and I'm unsure of how to get this to work again.

Paul Newcome

@Vince K The only two fixes are to hope it clears itself out after some time or contact support. But the re-ordering and updating of the source data could have also had an impact on the formula.