Mark G asks in Create a random playlist in excel:

Can this example be modified to create a true round robin draw? i.e. using a 6 team scenario, for any round there will be three games/matches, but obviously Team A could not compete against say Team B and Team C during that round as can occur using the above formula.

A round-robin tournament or all-play-all tournament is a type of tournament in which each contestant meets all other contestants in turn. Source: Wikipedia

This example is on a double round-robin basis, in which every team plays all others in its league once at home and once away randomly.

I am very close to a solution but I am stuck. This is as far as I get.

Problem: Sometimes a #num error is rendered in column D.

### Array formula in cell C2:

=INDEX(\$A\$2:\$A\$5, LARGE((COUNTIF(\$C\$1:C1, \$A\$2:\$A\$5)<3)*(COUNTIF(\$C\$1:D1, \$A\$2:\$A\$5)<ROUNDUP(ROWS(\$C\$1:D1)/2, 0))*ROW(\$1:\$4), RANDBETWEEN(1, SUMPRODUCT(--(COUNTIF(\$C\$1:C1, \$A\$2:\$A\$5)<3)*(COUNTIF(\$C\$1:D1, \$A\$2:\$A\$5)<ROUNDUP(ROWS(\$C\$1:D1)/2, 0)))))) + CTRL + SHIFT + ENTER.

### Array formula in cell D2:

=INDEX(\$A\$2:\$A\$5, LARGE((COUNTIF(\$D\$1:D1, \$A\$2:\$A\$5)<3)*(COUNTIF(\$C\$1:D1, \$A\$2:\$A\$5)<ROUNDUP(ROWS(\$C\$1:D1)/2, 0))*(COUNTIF(C2, \$A\$2:\$A\$5)=0)*(ISERROR(MATCH(\$A\$2:\$A\$5, IF(C2=\$C\$1:C1, \$D\$1:D1, ""), 0)))*ROW(\$1:\$4), RANDBETWEEN(1, SUMPRODUCT(--((ISERROR(MATCH(\$A\$2:\$A\$5, IF(C2=\$C\$1:C1, \$D\$1:D1, ""), 0)))*(COUNTIF(\$C\$1:D1, \$A\$2:\$A\$5)<ROUNDUP(ROWS(\$C\$1:D1)/2, 0))*(COUNTIF(\$D\$1:D1, \$A\$2:\$A\$5)<3)*(COUNTIF(C2, \$A\$2:\$A\$5)=0))))))+ CTRL + SHIFT + ENTER.

### This is how the formula in cell c2 and d2 works (simplified)

Index(Teams, Large(Row(Teams)*(Criterion1)*(Criterion2)), Randbetween(1, Sumproduct((Criterion1)*(Criterion2)))) + CTRL + SHIFT + ENTER.

The formula selects a team randomly in column C and column D, with the use of each criterion.

Formula criteria in column C

1. COUNTIF(\$C\$1:C1, \$A\$2:\$A\$5)<3. Each team can only play each opponent once, thus totally three times in column C.
2. COUNTIF(\$C\$1:D1, \$A\$2:\$A\$5)<ROUNDUP(ROWS(\$C\$1:D1)/2, 0). Each team can only play once in a round

Formula criteria in column D

1. COUNTIF(\$D\$1:D1, \$A\$2:\$A\$5)<3. Each team can only play each opponent once, thus totally three times in column D.
2. COUNTIF(\$C\$1:D1, \$A\$2:\$A\$5)<ROUNDUP(ROWS(\$C\$1:D1)/2, 0. Each team can only play once in a round
3. COUNTIF(C2, \$A\$2:\$A\$5)=0. A team can not play against it self
4. ISERROR(MATCH(\$A\$2:\$A\$5, IF(C2=\$C\$1:C1, \$D\$1:D1, ""), 0). There can not be any duplicate matches. Each team can only play once against each opponent "home" and once "away".

Sometimes the formula creates a true round-robin tournament, sometimes it creates  a #num error in column D. This is because a random team in column C is not valid. It creates a duplicate match and returns a #num error in column D.

If  I add another criteria in column C containing adjacent cell in column D a circular reference is created.

true round robin draw.xls
(Excel 97-2003 Workbook *.xls)

SUMPRODUCT(array1, array2, )
Returns the sum of the products of the corresponding ranges or arrays

COUNTIF(range,criteria)
Counts the number of cells within a range that meet the given condition

LARGE(array,k) returns the k-th largest row number in this data set.

ROW(reference) returns the rownumber of a reference

INDEX(array,row_num,[column_num])
Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

RANDBETWEEN(bottom, top)
Returns a random number between the numbers you specify

ISERROR(value)
Checks whether a value is an error and returns TRUE or FALSE

MATCH(lookup_value;lookup_array; [match_type])
Returns the relative position of an item in an array that matches a specified value