Dynamic Arrays
List all combinations from two lists (Cartesian product with TOCOL)
Generate every Size x Color SKU variant or Region x Quarter pair into a single spilled column using TOCOL(List1 & "-" & TRANSPOSE(List2)).
Broadcast vertical x horizontal arrays, then flatten with TOCOL
When you concatenate an M-row vertical column A2:A4 ("S", "M", "L") with an N-column horizontal row TRANSPOSE(B2:B3) ("Black", "Navy"), dynamic-array broadcasting creates an M x N 2D matrix of all 6 combinations automatically!
Wrapping that 2D matrix inside TOCOL(...) flattens it row-by-row into a single clean 6-row SKU column ("S-Black", "S-Navy", "M-Black", ...).
| Sizes (A2:A4) | x "Black" (B2) | x "Navy" (B3) | Flattened TOCOL Order |
|---|---|---|---|
| S | S-Black | S-Navy | 1. S-Black, 2. S-Navy |
| M | M-Black | M-Navy | 3. M-Black, 4. M-Navy |
| L | L-Black | L-Navy | 5. L-Black, 6. L-Navy |
Cartesian SKU generator and total-variant count formulas
Even better: chain a 3rd list by wrapping a second TOCOL(Step1 & "-" & TRANSPOSE(List3)) around the first!
=TOCOL(A2:A4 & "-" & TRANSPOSE(B2:B3))Total combinations generated (M * N)
For these fully populated lists, the row count equals ROWS(A2:A4)*ROWS(B2:B3). Blank range elements still participate in the product.
=ROWS(TOCOL(A2:A4 & "-" & TRANSPOSE(B2:B3)))Additional worked calculation
Using the initial example data:
=TEXTJOIN(", ", TRUE, TOCOL(A2:A4 & "-" & TRANSPOSE(B2:B3)))Additional worked calculation
Using the initial example data:
=INDEX(TOCOL(A2:A4 & "-" & TRANSPOSE(B2:B3)), 4, 1)Inputs and interpretation
The output size is the product of the input range sizes. Use bounded nonempty lists and leave enough empty spill space; large products grow quickly. Duplicate inputs create duplicate combinations, and blank entries are still range elements. Delimiters inside values can make joined identifiers ambiguous.
Try the example
Use the original values or change them to test your understanding.