# 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:

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.

The following tutorials explain how to perform other common tasks in Excel:

May 13, 2024
April 25, 2024
April 19, 2024
April 18, 2024
April 18, 2024

## One Reply to “Excel: How to List All Possible Combinations Between Lists”

1. Gina says:

Hi Zack can this be used for multiple lists – up to 5 lists?