How to Generate Dynamic Permutations and Combinations in Microsoft Excel Using REDUCE and LAMBDA

In financial modeling, inventory planning, product configuration management, and statistical analysis, business analysts frequently need to generate all possible combinations or permutations from several parameter lists (such as Shirt Size × Colour × Fabric, or investment portfolio weight allocations). Historically, generating a complete Cartesian product or set of distinct permutations in Microsoft Excel required complex VBA macros, cumbersome Power Query cross-joins, or fragile multi-cell array drag-down formulas. With modern Excel dynamic array functions and LAMBDA helper functions (notably REDUCE, TOCOL, and TOROW), analysts can construct robust, fully dynamic combinatorial generators that automatically spill results across the worksheet.

Cartesian Products vs Permutations Explained

Before constructing dynamic array formulas, understanding the mathematical distinction is essential:

  • Cartesian Product (Combinations Across Sets): Combines elements from two or more independent lists such that every item in List A is paired with every item in List B (e.g. 3 sizes × 4 colours = 12 total variations).
  • Permutations (Ordered Arrangements of a Single Set): Generates all possible ordered sequences of items chosen from a single list without repetition (e.g. arranging 3 runners into 1st, 2nd, and 3rd place finishes: $3! = 6$ permutations).
  • Dynamic Array Spilling: Rather than copying formulas down thousands of rows, a single dynamic array formula evaluates the source ranges and spills the entire matrix automatically into adjacent cells.

Generating a Two-List Cartesian Product Using TOCOL and TOROW

The simplest combinatorial requirement involves generating all possible pairs between two vertical lists (for instance, Column A contains Sizes: Small, Medium, Large; Column B contains Colours: Red, Blue, Green).

=TOCOL(A2:A4 & " - " & TOROW(B2:B4))

How the formula evaluates:

  1. TOROW(B2:B4) transposes the second vertical column into a horizontal row in memory.
  2. The string concatenation operator (&) automatically broadcasts the vertical 3×1 array against the horizontal 1×3 array, producing a 3×3 two-dimensional grid of paired strings.
  3. TOCOL() flattens this 2D grid into a clean, single vertical column containing all 9 variations.

Generating Multi-List Combinations Using REDUCE and LAMBDA

When combinations span three or more attributes (such as Size, Colour, Material, and Region), nesting multiple TOROW operations becomes unmanageable. The REDUCE function solves this by iteratively folding additional columns into an accumulating combinatorial array:

=DROP(REDUCE("", A2:A4, LAMBDA(acc, size,
    VSTACK(acc, TOCOL(size & " | " & TOROW(B2:B4)))
)), 1)

To extend this across three dynamic lists (e.g. Column A: Sizes, Column B: Colours, Column C: Materials):

=LET(
    Sizes, A2:A4,
    Colours, B2:B4,
    Materials, C2:C3,
    Step1, TOCOL(Sizes & " - " & TOROW(Colours)),
    Step2, TOCOL(Step1 & " - " & TOROW(Materials)),
    Step2
)

The LET function isolates intermediate variables, preventing duplicate calculations and keeping the formula readable.

Generating Permutations of a Single List Without Duplicates

To generate all 2-item ordered permutations from a single list of items (e.g. A, B, C, D in range A2:A5) while excluding self-pairings (like A-A or B-B):

=LET(
    Items, A2:A5,
    Grid, TOCOL(Items & " > " & TOROW(Items)),
    Filtered, FILTER(Grid, ISERROR(FIND(Items & " > " & Items, Grid))),
    Filtered
)

How this achieves true permutations:

  1. The Cartesian product creates all pairings including self-matches (A>A, A>B, A>C, etc.).
  2. FILTER() evaluates each row and discards pairings where the first item matches the second item, leaving only valid permutations.

Separating Combinatorial Results into Multiple Columns

When exporting combination sets to inventory management software or SQL databases, attributes must reside in independent columns rather than concatenated strings. Use TEXTSPLIT wrapped in a BYROW or MAKEARRAY helper:

=LET(
    ComboList, TOCOL(A2:A4 & "|" & TOROW(B2:B4)),
    DROP(REDUCE("", ComboList, LAMBDA(acc, row,
        VSTACK(acc, TEXTSPLIT(row, "|"))
    )), 1)
)

The resulting matrix spills across two distinct columns, displaying Size in Column 1 and Colour in Column 2.

Best Practices and Calculation Performance

When working with dynamic combinatorial arrays in Excel:

  • Combinatorial Explosion: Be mindful of array size limits. Combining five lists with 10 elements each generates $10^5 = 100,000$ rows, which recalculates quickly, but ten lists with 10 elements each produces 10 billion rows, exceeding Excel’s 1,048,576-row sheet limit.
  • Wrap in Table References: Reference dynamic Excel Tables (e.g. Table_Sizes[Size]) instead of hardcoded ranges (A2:A4) so new attributes automatically expand the output array.
  • Check for #SPILL! Errors: Ensure that cells directly below and to the right of your formula are completely blank; any obstructing cell will prevent Excel from rendering the combination matrix.

Get the best tech tips delivered straight to your inbox.

Join thousands of readers mastering Apple, Google, Microsoft, and Linux.