Return Arrays with Excel’s LAMBDA Function

Return Arrays with Excel’s LAMBDA Function
Last Updated on 12/05/2024
Reading time: 2 minutes

The Excel LAMBDA function allows users to create custom without programming, like VBA. It transforms complex formulas into simple functions that can handle arrays and return dynamic results. In this step-by-step guide, let's see how to create a Lambda function that will return an array.

Advantages of Creating an Array LAMBDA Function

Imagine you have a dashboard displaying monthly sales per employee. Each month, you need to calculate the SUM, AVERAGE, Standard Deviation, MIN, and MAX. While these functions are straightforward to write, it's easy to mistakenly select the wrong cell range, leading to calculation errors.

However, by creating an Array LAMBDA function that simultaneously returns five results, you can ensure the accuracy of all your calculations.

One LAMBDA function returns an array of results

How to return an Array with Lambda?

#1: Open AFE (Advanced Function Editor)

This task isn't compulsory but it's more convenient to write your Lambda function with this tool.

  1. Open AFE
  2. Go to the Module tab
Module Tab of the Excel Labs AFE tool

#2: Write the beginning of your LAMBDA

  1. Give a name to your Lambda function
  2. Write the Lambda name
  3. Give a name for your range of cells as an argument.
Begining of the LAMBDA function

#3: Write your 5 formulas in a LET function

The LET function is an easy way to simplify the writing of long functions. You can give a name for each one of your calculations.

The 5 formulas in the LET function

#4: Gather the 5 formulas in an Array

The trick is constructing an array using these five formulas. Fortunately, Excel's VSTACK function, designed specifically for this purpose, makes this process simple.

=VSTACK(formula1, formula2, ....)

The array lambda to return 5 calculations

And that's all! Now, in your Excel workbook, you write your Lambda function name, you select your range, and you return 5 calculations 😀👍

2 Comments

  1. Rick Rothstein
    13/05/2024 @ 15:26

    Of course, you could write your LAMBDA without using variables as the function names are pretty much self-explanatory...
    MyAnalysis = LAMBDA(MyData,
    VSTACK(
    SUM(MyData),
    AVERAGE(MyData),
    STDEV(MyData),
    MIN(MyData),
    MAX(MyData)
    )
    )

    Reply

    • Frédéric LE GUEN
      13/05/2024 @ 15:28

      Sure Rick, you're right. But here the example was more to explain the VSTACK than to optimize the formula. At least, I appreciate that you find that useful

      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Return Arrays with Excel’s LAMBDA Function

Reading time: 2 minutes
Last Updated on 12/05/2024

The Excel LAMBDA function allows users to create custom without programming, like VBA. It transforms complex formulas into simple functions that can handle arrays and return dynamic results. In this step-by-step guide, let's see how to create a Lambda function that will return an array.

Advantages of Creating an Array LAMBDA Function

Imagine you have a dashboard displaying monthly sales per employee. Each month, you need to calculate the SUM, AVERAGE, Standard Deviation, MIN, and MAX. While these functions are straightforward to write, it's easy to mistakenly select the wrong cell range, leading to calculation errors.

However, by creating an Array LAMBDA function that simultaneously returns five results, you can ensure the accuracy of all your calculations.

One LAMBDA function returns an array of results

How to return an Array with Lambda?

#1: Open AFE (Advanced Function Editor)

This task isn't compulsory but it's more convenient to write your Lambda function with this tool.

  1. Open AFE
  2. Go to the Module tab
Module Tab of the Excel Labs AFE tool

#2: Write the beginning of your LAMBDA

  1. Give a name to your Lambda function
  2. Write the Lambda name
  3. Give a name for your range of cells as an argument.
Begining of the LAMBDA function

#3: Write your 5 formulas in a LET function

The LET function is an easy way to simplify the writing of long functions. You can give a name for each one of your calculations.

The 5 formulas in the LET function

#4: Gather the 5 formulas in an Array

The trick is constructing an array using these five formulas. Fortunately, Excel's VSTACK function, designed specifically for this purpose, makes this process simple.

=VSTACK(formula1, formula2, ....)

The array lambda to return 5 calculations

And that's all! Now, in your Excel workbook, you write your Lambda function name, you select your range, and you return 5 calculations 😀👍

2 Comments

  1. Rick Rothstein
    13/05/2024 @ 15:26

    Of course, you could write your LAMBDA without using variables as the function names are pretty much self-explanatory...
    MyAnalysis = LAMBDA(MyData,
    VSTACK(
    SUM(MyData),
    AVERAGE(MyData),
    STDEV(MyData),
    MIN(MyData),
    MAX(MyData)
    )
    )

    Reply

    • Frédéric LE GUEN
      13/05/2024 @ 15:28

      Sure Rick, you're right. But here the example was more to explain the VSTACK than to optimize the formula. At least, I appreciate that you find that useful

      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *