COMBINATIONS Function in Excel

Work in Progress :: Please contact us to report errors, typos, etc.

=L_COMBINATIONS(array_1, array_2)

Argument	Description	Example
array_1	An array or range of numbers or text	{"A";"B";"C"}
array_2	An array or range of numbers or text	{1;2;3}

Description

L_COMBINATIONS returns an array in which each row of array_1 is paired with each of the rows of array_2 for a total of ROWS(array_1)*ROWS(array_2) combinations.

The L_COMBINATIONS function can be used to form combinations of values between any number of arrays, through nesting the function. For example, to form an array of all combinations of values within 3 different arrays, use:

=L_COMBINATIONS( L_COMBINATIONS(array1,array2), array3 )

COMBINATIONS Function Example

L_COMBINATIONS uses two other lambda functions: L_REPELEM creates the first column (or set of columns) from array_1 by repeating the elements vertically. L_REPARRAY creates the second column (or set of columns) by repeating the entire array_2 vertically. HSTACK combines the two sets.

Finding combinations is useful in many different scenarios. Here are a few example uses:

Creating a Mesh Grid: In mathematical modeling or 3D plotting, you may have two sets of arrays representing values for X and Y. The combinations of these values creates a grid that you can use for evaluating a Z-value.
Model Exploration and Optimization: When you have multiple sets of parameters, you may want to try every possible combination of input values to perform a global optimization.
Experimental Designs: When performing statistical testing with multiple factors, creating an array of all possible combinations of factors allows you to set up a fully crossed design or full factorial design.
Game Theory and Analysis: You may have a separate set of strategies for two different players and you want to analyze the outcome of all combinations of these strategies.
Product Mix Decisions: An array may represent different products and a second array various packaging options. Evaluating all combinations may help in decision regarding product mixes.

Lambda Formula

This code for using L_COMBINATIONS in Excel is provided under the License as part of the LAMBDA Library, but to use just this function, you may copy the following code directly into your spreadsheet.

Code to Create Function via the Name Manager

Name: L_COMBINATIONS
Comment: Returns an array of all combinations of rows within two different arrays
Refers To:

= LAMBDA(array_1,array_2,
LET(doc,"https://www.vertex42.com/lambda/combinations.html",
    r_1,ROWS(array_1),r_2,ROWS(array_2),
    first,L_REPELEM(array_1,r_2,1),
    second,L_REPARRAY(array_2,r_1,1),
    HSTACK(first,second)
))

Code for AFE Workbook Module (Excel Labs Add-in)

/**
* Return an array of all combinations of rows from two arrays
*/
L_COMBINATIONS = LAMBDA(array_1,array_2,
LET(doc,"https://www.vertex42.com/lambda/combinations.html",
    r_1,ROWS(array_1),r_2,ROWS(array_2),
    first,L_REPELEM(array_1,r_2,1),
    second,L_REPARRAY(array_2,r_1,1),
    HSTACK(first,second)
));

Named Function for Google Sheets

Name: L_COMBINATIONS
Description: Returns an array of all combinations of rows within two different arrays
Arguments: array_1, array_2 (see above for descriptions and example values)
Function:

LET(doc,"https://www.vertex42.com/lambda/combinations.html",
    r_1,ROWS(array_1),r_2,ROWS(array_2),
    first,L_REPELEM(array_1,r_2,1),
    second,L_REPARRAY(array_2,r_1,1),
    HSTACK(first,second)
)

L_COMBINATIONS Examples

Example

Generate an array where the rows represent all possible combinations of values from two different vectors. The first column represents the values from the first array and the second column represents values from the second array. Each row is a different combination.

Test: Copy and Paste this LET function into a cell
=LET(
    vector_1, {1;5;3},
    vector_2, {10;50;30},
    L_COMBINATIONS(vector_1,vector_2)
)

Result: {1,10;1,50;1,30;5,10;5,50;5,30;3,10;3,50;3,30}

L_COMBINATIONS