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