Author: Oscar Cronquist Article last updated on January 02, 2019

This blog article describes how to create teams randomly. There are twenty names in column B and four teams in column E. There will be five team members in each team. In column C a team is randomly selected in each cell.

Array formula in cell C2:

=INDEX($E$2:$E$5, LARGE((COUNTIF($C$1:C1, $E$2:$E$5)<5)*(ROW($1:$4)), RANDBETWEEN(1, SUMPRODUCT(--(COUNTIF($C$1:C1, $E$2:$E$5)<5)))))

How to create an array formula

  1. Copy the aray formula above (Ctrl + c)
  2. Double click cell B2
  3. Paste (Ctrl + v)
  4. Press and hold Ctrl + Shift simultaneously
  5. Press Enter
  6. Release all keys

If you made the above steps correctly the formula now has a beginning and ending curly bracket, like this:

Don't enter these characters yourself, they appear automatically.

Customize formula

COUNTIF($C$1:C1, $E$2:$E$5)<5 makes sure that each team has max five team members.

ROW($1:$4) gives a row number to each team. Example, if you have three teams, change it to ROW($1:$3) or the formula won't work.

Explaining formula in cell C2

Step 1 - Make sure that not more than 5 names have been assigned the same team

The COUNTIF function counts values based on a condition or criteria, the first argument contains an expanding cell reference, it grows when the cell is copied to cells below. This makes the formula aware of values displayed in cells above.

COUNTIF($C$1:C1, $E$2:$E$5)<5


COUNTIF("Team",{"Team A"; "Team B"; "Team C"; "Team D"})<5


{0; 0; 0; 0}<5

and returns {TRUE; TRUE; TRUE; TRUE}.

Step 2 - Multiply with array containing row numbers

(COUNTIF($C$1:C1, $E$2:$E$5)<5)*(ROW($1:$4))




{TRUE; TRUE; TRUE; TRUE}*{1;2;3;4}

and returns


Step 3 - Extract random row number

The LARGE function returns the k-th largest number, k is calculated based on how many teams that have been shown up until this cell.

LARGE((COUNTIF($C$1:C1, $E$2:$E$5)<5)*(ROW($1:$4)), RANDBETWEEN(1, SUMPRODUCT(--(COUNTIF($C$1:C1, $E$2:$E$5)<5))))


LARGE({1;2;3;4}, RANDBETWEEN(1, SUMPRODUCT(--(COUNTIF($C$1:C1, $E$2:$E$5)<5))))

SUMPRODUCT function returns the number of team names that have not yet been fully assigned to players.



LARGE({1;2;3;4}, RANDBETWEEN(1, SUMPRODUCT({1; 1; 1; 1})))


LARGE({1;2;3;4}, RANDBETWEEN(1, 4))


LARGE({1;2;3;4}, 3).

and returns 3. This is a random number.

Step 4 - Return value

The INDEX function returns a value based on a cell reference and column/row numbers.

INDEX($E$2:$E$5, LARGE((COUNTIF($C$1:C1, $E$2:$E$5)<5)*(ROW($1:$4)), RANDBETWEEN(1, SUMPRODUCT(--(COUNTIF($C$1:C1, $E$2:$E$5)<5)))))


INDEX($E$2:$E$5, 3)

and returns "Team C" in cell C2.

Download Excel file


This article demonstrates how to generate teams of different sizes:

How to build a Team Generator – different number of people per team

JD asks in this post: Dynamic team generator Hi, what if we have different number of people per team? So in […]

How to build a Team Generator – different number of people per team

The following shows you how to set up a dynamic scoreboard, enter results and the scoreboard is automatically recalculated:

Dynamic scoreboard

This article demonstrates a scoreboard, displayed to the left, that sorts contestants based on total scores and refreshes instantly each […]

Dynamic scoreboard