Excel: How to List All Possible Combinations Between Lists

You can use the following formula to list all possible combinations between multiple lists in Excel:

=IF(ROW()-ROW($D$2)+1>COUNTA($A$2:$A$4)*COUNTA($B$2:$B$4),"",INDEX($A$2:$A$4,INT((ROW()-ROW($D$2))/COUNTA($B$2:$B$4)+1))&" "&INDEX($B$2:$B$4,MOD(ROW()-ROW($D$2),COUNTA($B$2:$B$4))+1))

This particular formula lists all possible combinations between the values in the range A2:A4 and the range B2:B4, and outputs these combinations starting in cell D2.

The following example shows how to use this formula in practice.

Example: List All Possible Combinations Between Multiple Lists in Excel

Suppose we have a list of basketball team names and a list of positions:

Suppose we would like to list all possible combinations between the team names and the positions.

To do so, we can type the following formula into cell D2:

=IF(ROW()-ROW($D$2)+1>COUNTA($A$2:$A$4)*COUNTA($B$2:$B$4),"",INDEX($A$2:$A$4,INT((ROW()-ROW($D$2))/COUNTA($B$2:$B$4)+1))&" "&INDEX($B$2:$B$4,MOD(ROW()-ROW($D$2),COUNTA($B$2:$B$4))+1))

We can then click and drag this formula down to more cells in column D until the formula stops producing results:

Excel list all possible combinations

Column D now contains all possible combinations between the team names and the positions.

We can see that there are a total of 9 possible combinations.

If you’d like, you can display the combinations in separate columns by typing the following formula into cell E2:

=TEXTSPLIT(D2, " ")

You can then click and drag this formula down to each remaining cell in column E:

All of the possible combinations between the team names and positions are now displayed in two columns.

Note that the TEXTSPLIT function in Excel splits the text in a cell into multiple columns based on a delimiter.

In this example, we specified that the TEXTSPLIT function should use a space as a delimiter to decide where to split the text in each cell in column D.