Table of Contents
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.