Join Collect

Karrie Davis

Hi i'm trying to find all of the timekeepers that worked for a specific client. 

 

Normally I would use Index Match however,  there are multiple time keepers that worked for the client and index match only shows one name.

For Index match I use the following formula: =INDEX({TimeKeeper}, MATCH([Matter Number]1, {Matter Number}, 0))

 

I tried Join collect but I keep getting a blank square could some assist.  

=JOIN(COLLECT({TimeKeeper}, {Matter Number}, [Matter Number]1))

 

Thanks

L_123

Your formula looks correct. Have you double checked the references to make sure they didn't get messed up somehow? just click into the formula, then put the cursor on the reference, and select "Edit Reference". Make sure what you are referencing is highlighted, and that both ranges have the same amount of values.

Karrie Davis

Thank you,

I checked however it is still showing a blank square and everything matches.

Paul Newcome

If your Matter Numbers are actual numeric values, it will struggle to make a match. Try changing the Matter Numbers to text strings.

L_123

It already is text. Smartsheet only supports 5 decimals and he has 6 so he can't be showing it as a value. Didn't know this until I tried to recreate his problem

L_123

Just FYI, your formula should work. No reason it shouldn't. But I found a workaround that forces it to do what you want.

=JOIN(COLLECT({TimeKeeper}, {Matter Number}, @cell =[Matter Number]1))

For future reference if you have issues with a collect formula, try tossing some @cell references at your criteria. It really generally makes collect function more predictably.

Karrie Davis

thank you, this worked