Formula to pair staff based on criteria

ABoado

Hello Smartsheet community,

An idea came up recently about pairing staff up who work in different divisions for a random coffee chat to increase staff engagement. We could use Smartsheet to create a form to gather info on staff who are interested in participating and what division they belong in but is there a formula I can use in the sheet that can match staff up based on the criteria that they are not in the same division? And then when staff are matched, is there a way to set up an automated workflow in which the matched staff are notified that they have been paired? Any ideas are welcome! I was thinking Index Match and a helper sheet might be helpful here but wasn't sure. Thanks in advance!!

Genevieve P.

Hi @ABoado

What a fun idea! 🙂

You could use a JOIN(COLLECT function to show all possible matches per person based on your criteria (not the same department):

=JOIN(COLLECT(Name:Name, Department:Department, <>Department@row), CHAR(10))

Using this column as a guide, select a name from the list and type it into a manual-entry Text/Number column:

Then, I have a "Final Match" column set up then based on this Test Match that I entered to show the matching pair for each person. This is set as a column formula.

=IF([Test Match]@row <> "", [Test Match]@row, IF(COUNTIF([Test Match]:[Test Match], Name@row) > 0, INDEX(Name:Name, MATCH(Name@row, [Test Match]:[Test Match], 0))))

I set up a conditional formatting rule to turn the blank Test Match cells grey if that person has a match based on a previous selection. That way I don't accidentally put in a second match for someone (such as Joe on the second row).

Finally, I adjusted the "Possible Matches" column to eliminate options once they've been selected as a final match, so you only see new possible matches to write in your Test Match column:

=IF([Final Match]@row <> "", "MATCH FOUND", JOIN(COLLECT(Name:Name, Department:Department, <>Department@row, [Final Match]:[Final Match], ""), CHAR(10)))

You can of course rearrange columns or change the formatting if it's easier to see the matches together:

I hope this helps!

Cheers,

Genevieve