Answer:

The image above demonstrates a worksheet containing Team names in an Excel defined table cell range F3:F7, you don't need to adjust cell references in the formula if you add or delete team names. It is dynamic and updates instantly.

Enter the number of members in each team in cell H3, the formula refreshes automatically. It randomly assigns teams to players, press F9 to refresh the formula output.

Array formula in cell D3:

To enter an array formula, type the formula in a cell then press and hold CTRL + SHIFT simultaneously, now press Enter once. Release all keys.

The formula bar now shows the formula with a beginning and ending curly bracket telling you that you entered the formula successfully. Don't enter the curly brackets yourself.

Copy cell D3 and paste it down as far as necessary.

Explaining formula in cell D3

Step 1 - Count previous values

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. 0 (zero) indicates values that not yet have been displayed.

COUNTIF($D$2:D2, Table1[Teams])<$H$3

becomes

COUNTIF("Team", {"Team A"; "Team B"; "Team C"; "Team D"; "Team E"})<$H$3

becomes

{0;0;0;0;0}<3

and returns

{TRUE; TRUE; TRUE; TRUE; TRUE}

Step 2 - Create row numbers

This step is needed to create a sequence of numbers that will be used to get the correct value in a later step.

MATCH(ROW(Table1[Teams]),ROW(Table1[Teams]))

becomes

MATCH(ROW({"Team A"; "Team B"; "Team C"; "Team D"; "Team E"}),ROW({"Team A"; "Team B"; "Team C"; "Team D"; "Team E"}))

becomes

MATCH({3;4;5;6;7}, {3;4;5;6;7})

and returns {1;2;3;4;5}.

Step 3 - Multiply arrays

(COUNTIF($D$2:D2, Table1[Teams])<$H$3)*(MATCH(ROW(Table1[Teams]),ROW(Table1[Teams])))

becomes

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

and returns {1;2;3;4;5}.

Step 4 - Extract random row number

The LARGE function returns the k-th largest number, LARGE( array , k). The second argument k is a random number from 1 to n.

LARGE((COUNTIF($D$2:D2, Table1[Teams])<$H$3)*(MATCH(ROW(Table1[Teams]),ROW(Table1[Teams]))), RANDBETWEEN(1, SUMPRODUCT(--(COUNTIF($D$2:D2, Table1[Teams])<$H$3))))

becomes

LARGE({1;2;3;4;5}, RANDBETWEEN(1, SUMPRODUCT(--(COUNTIF($D$2:D2, Table1[Teams])<$H$3))))

becomes

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

becomes

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

becomes

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

and returns 2. This is a random number between 1 and 5.

Step 5 - Return value

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

INDEX(Table1[Teams], LARGE((COUNTIF($D$2:D2, Table1[Teams])<$H$3)*(MATCH(ROW(Table1[Teams]),ROW(Table1[Teams]))), RANDBETWEEN(1, SUMPRODUCT(--(COUNTIF($D$2:D2, Table1[Teams])<$H$3)))))

becomes

INDEX(Table1[Teams], 2)

and returns "Team B" in cell D3.

Step 6 - Trap errors

The formula returns errors when all teams are populated, the IFERROR function converts the errors into a given value, in this case "-".

IFERROR(INDEX(Table1[Teams], LARGE((COUNTIF($D$2:D2, Table1[Teams])<$H$3)*(MATCH(ROW(Table1[Teams]),ROW(Table1[Teams]))), RANDBETWEEN(1, SUMPRODUCT(--(COUNTIF($D$2:D2, Table1[Teams])<$H$3))))), "-")

Get the Excel file

Dynamic-team-generator.xlsx