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.

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

#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.

#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.

#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, ….)

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