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.

**Additional Resources**

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

Excel: How to Count Unique Names

Excel: How to Count Unique Values by Group

Excel: How to Generate Unique Identifiers

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