[Excel] Cartesian product

With a 6-year delay! But the issue remains relevant!

Here’s how I proceed:

I number each item in the two tables (from 1 to 3 for the first table and from 1 to 2 for the second). The pair of values being sought, like (c,2) or c2, will have coordinates (3,2); it will be obtained by decoding the coordinates with two VLOOKUP(...) or two INDEX(...), one for the first coordinate and the other for the second coordinate.

Now, it's just a matter of obtaining the different possible coordinates. This can be easily done by taking all the numbers from 1 to n*m where n is the size of the first table and m is the size of the second table (here from 1 to 3*2, which is from 1 to 6). This is obtained using the following formula:

Calculating the Cartesian product of two tables

Calculating the Cartesian product of two tables Table A Table B 1 a 1 1 2 b 2 2 3 c Size of table A: 3 Size of table B: 2 Coord. 1 Coord. 2 Table AxB 1 =MOD($A13-1;$C$9)+1 =INT(($A13-1)/$C$9)+1 =INDEX($B$5:$B$7;$B13)&INDEX($D$5:$D$6;$C13) 2 =MOD($A14-1;$C$9)+1 =INT(($A14-1)/$C$9)+1 =INDEX($B$5:$B$7;$B14)&INDEX($D$5:$D$6;$C14) 3 =MOD($A15-1;$C$9)+1 =INT(($A15-1)/$C$9)+1 =INDEX($B$5:$B$7;$B15)&INDEX($D$5:$D$6;$C15) 4 =MOD($A16-1;$C$9)+1 =INT(($A16-1)/$C$9)+1 =INDEX($B$5:$B$7;$B16)&INDEX($D$5:$D$6;$C16) 5 =MOD($A17-1;$C$9)+1 =INT(($A17-1)/$C$9)+1 =INDEX($B$5:$B$7;$B17)&INDEX($D$5:$D$6;$C17) 6 =MOD($A18-1;$C$9)+1 =INT(($A18-1)/$C$9)+1 =INDEX($B$5:$B$7;$B18)&INDEX($D$5:$D$6;$C18) 

And here’s the result:

 Calculating the Cartesian product of two tables Table A Table B 1 a 1 1 2 b 2 2 3 c Size of table A: 3 Size of table B: 2 No. Coord. 1 Coord. 2 Table AxB 1 1 1 a1 2 2 1 b1 3 3 1 c1 4 1 2 a2 5 2 2 b2 6 3 2 c2 

Otherwise, there's a more basic way to manually create the Cartesian product table from a matrix:

 1 2 a =$G5&H$4 =$G5&I$4 b =$G6&H$4 =$G6&I$4 c =$G7&H$4 =$G7&I$4 

With the result:

Table AxB 1 2 a a1 a2 b b1 b2 c c1 c2 

Then all that's left to do is take the columns and stack them to get the desired table!