What LAMBDA does
LAMBDA defines a calculation using names for its inputs. You can call that calculation immediately in a worksheet formula, give it a reusable workbook name, or pass it to a function that supplies the inputs for you.
The distinction matters: defining a calculation doesn't run it. In the worksheet examples below, the second pair of parentheses supplies the values and calls the function.
Practical examples
Pass a sales range to a calculation
Enter 60, 40, and 25 in A2:A4. In C2, enter:
=LAMBDA(x,SUM(x))(A2:A4)FormulaThe expected result is 125. x is the input name, and SUM(x) is the calculation. The final (A2:A4) passes those three cells into x, so the calculation adds 60 + 40 + 25.
This small example shows the calling syntax. You could use SUM directly for this one calculation; LAMBDA becomes useful when you want to name a reusable rule or supply one to another function.
Compare two groups of sales
Keep 60, 40, and 25 in A2:A4. Enter 30, 20, and 15 in B2:B4. In D2, enter:
=LAMBDA(current,prior,SUM(current)-SUM(prior))(A2:A4,B2:B4)FormulaThe expected result is 60. The call passes A2:A4 into current and B2:B4 into prior. Their sums are 125 and 65, so the calculation returns 125 − 65 = 60.
Parameter order matters: the first supplied range belongs to the first parameter. Reversing the ranges would reverse the subtraction.
Common mistakes and notes
A definition by itself returns #CALC! in a cell
Entering =LAMBDA(x,SUM(x)) by itself in a worksheet cell defines the calculation but doesn't call it. Add the input in a second pair of parentheses, as in the first example, to get its result.
Functions such as GROUPBY and PIVOTBY can instead receive LAMBDA(x,SUM(x)) as an argument and call it for each group. In that situation, don't append a separate worksheet range: the grouping function supplies the values. The sales summary walkthrough uses this form.
Put the calculation last and match the input count
Every argument before the final calculation defines a parameter. If you declare two parameters, supply two arguments when calling the function. An incorrect argument count returns #VALUE!; defining more than 253 parameters also returns #VALUE!.
Use valid Excel names for parameters, and don't include a period in a parameter name. Too many recursive calls, where a function repeatedly calls itself, can produce #NUM!.
Save a tested definition when you want to reuse it
After checking the immediate-call formula, you can store the LAMBDA definition under a name with workbook scope. Other sheets in that workbook can then call the named function. Store the definition without the test call appended to it.
Evaluate Formula won't step inside the calculation
Excel's Evaluate Formula tool returns a LAMBDA's result but can't step through its internal calculation. Test the calculation with known inputs first; for the second example, checking the two SUM results separately makes the expected difference easy to verify.