Generate All Possible Combinations of Two Lists in Excel
This post will guide you how to generate all possible combinations with a formula in excel. How do I create list with all possible combinations of two lists in Excel. How to list all combinations from two separate list or range in Excel.
Generate All Possible Combinations of Two Lists in Excel
Assuming that you have two list of data in different column, A and B, and you want to get a list of all possible combinations from those two list in Column A and B. How to achieve it.
You can use a formula based on the IF function, the ROW function, the COUNTA function, The INDEX function and the MOD function. Like this:
=IF(ROW()ROW($F$1)+1>COUNTA(A:A)*COUNTA(B:B),"",INDEX(A:A,INT((ROW()ROW($F$1))/COUNTA(B:B)+1))&INDEX(B:B,MOD(ROW()ROW($F$1),COUNTA(B:B))+1))
Type this formula into Cell F1, and then drag the AutoFill Handle down column F until you get cells that look empty.
Note: if you want to use this formula into other cell, you need to change the absolute cell reference to that cell. Such as: form F1 to E1.
=IF(ROW()ROW($E$1)+1>COUNTA(A:A)*COUNTA(B:B),"",INDEX(A:A,INT((ROW()ROW($E$1))/COUNTA(B:B)+1))&INDEX(B:B,MOD(ROW()ROW($E$1),COUNTA(B:B))+1))
Related Functions

Excel INDEX function
The Excel INDEX function returns a value from a table based on the index (row number and column number)The INDEX function is a buildin function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the INDEX function is as below:= INDEX (array, row_num,[column_num])… 
Excel ROW function
The Excel ROW function returns the row number of a cell reference.The ROW function is a buildin function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the ROW function is as below:= ROW ([reference])…. 
Excel IF function
The Excel IF function perform a logical test to return one value if the condition is TRUE and return another value if the condition is FALSE. The IF function is a buildin function in Microsoft Excel and it is categorized as a Logical Function.The syntax of the IF function is as below:= IF (condition, [true_value], [false_value])…. 
Excel MOD function
he Excel MOD function returns the remainder of two numbers after division. So you can use the MOD function to get the remainder after a number is divided by a divisor in Excel. The syntax of the MOD function is as below:=MOD (number, divisor)…. 
Excel INT function
The Excel INT function returns the integer portion of a given number. And it will rounds a given number down to the nearest integer.The syntax of the INT function is as below:= INT (number)… 
Excel COUNTA function
The Excel COUNTA function counts the number of cells that are not empty in a range. The syntax of the COUNTA function is as below:= COUNTA(value1, [value2],…)…