Table of Contents
To list all possible combinations between lists using Excel, one can use the “COMBIN” and “PERMUT” functions. These functions allow the user to specify the number of elements to be selected from the list and the total number of elements in the list, resulting in all possible combinations being displayed. This can be useful for generating various scenarios or outcomes in a structured manner, such as in financial modeling or data analysis. By using these functions, users can efficiently generate and organize comprehensive lists of combinations between multiple lists, saving time and effort.
Excel: 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.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel: