LAMBDA Function

Excel Functions › Logical

Excel 365

The Excel LAMBDA function lets you write your own custom functions — no VBA, no add-ins. You wrap a calculation and its parameters in LAMBDA, save it under a name, and from then on you call it like any built-in function. It is also the engine behind MAP, REDUCE, BYROW, BYCOL, MAKEARRAY, and SCAN.

Quick answer: a LAMBDA names its inputs, then uses them in a calculation. To test one, call it inline with arguments in a second set of parentheses:
=LAMBDA(x, x*2)(5) // returns 10
Once it works, save it in Name Manager under a name like DOUBLE and call =DOUBLE(5) anywhere in the workbook.

Syntax

=LAMBDA([parameter1, parameter2, …,] calculation)
ArgumentDescription
parameter1, …OptionalNames for the values the function will receive — up to 253 of them. A name must start with a letter, can’t contain spaces, and can’t look like a cell reference (so rate is fine, R1 is not).
calculationRequiredThe formula to evaluate, written using the parameter names. It must be the last argument, and it is the only one that’s required.

Available in: Excel for Microsoft 365 and Excel for the web only. Excel 2021 and earlier show #NAME?. A LAMBDA on its own returns nothing useful — it has to be called with arguments, either inline or through a defined name.

Step 1: build and test it inline

Before naming anything, prototype the function in a cell. Append the test arguments in parentheses right after the closing parenthesis of the LAMBDA:

=LAMBDA(x, x*2)(5) // x becomes 5, so the result is 10
=LAMBDA(f, (f-32)*5/9)(212) // Fahrenheit to Celsius: returns 100
=LAMBDA(a, b, SQRT(a^2 + b^2))(3, 4) // two parameters: returns 5

That trailing (5) is the part everyone forgets. Without it you defined a function but never ran it — Excel answers with #CALC!.

Step 2: name it — the killer feature

Calling a LAMBDA inline is just for testing. The real power move is saving it as a named function:

  1. Go to Formulas › Name Manager › New.
  2. Name: FAHRENHEIT (this becomes the function name).
  3. Comment: Converts Fahrenheit to Celsius — this shows up as the tooltip.
  4. Refers to: =LAMBDA(f, (f-32)*5/9) — note: no trailing arguments here.
  5. Click OK. Now call it anywhere in the workbook like a native function:
=FAHRENHEIT(A2) // reads like a built-in function
AB
1FahrenheitCelsius
2320.0
37222.2
498.637.0
5212100.0

Change the conversion logic once in Name Manager and every cell that calls FAHRENHEIT updates. That’s the maintenance win: business logic lives in one place instead of being copy-pasted into hundreds of cells.

Try it: build a LAMBDA live

Live demo

Pick a calculation, feed it an input, and watch the inline call evaluate.

Power moves: LET, helpers, and recursion

Inside a LAMBDA, LET keeps multi-step logic readable by naming intermediate results:

=LAMBDA(price, qty, LET(net, price*qty, tax, net*0.0825, net + tax)) // save as ORDER_TOTAL

LAMBDAs are also what you pass to the array helpers — each one feeds your function a different slice of data:

=MAP(A2:A50, LAMBDA(v, v*1.0825)) // MAP: one element at a time
=BYROW(B2:E20, LAMBDA(r, SUM(r))) // BYROW: one whole row at a time
=REDUCE(0, A2:A50, LAMBDA(acc, v, acc + MAX(v, 0))) // REDUCE: folds to a single value

And because a named LAMBDA can call itself, recursion is possible — the classic example strips every digit from a text string by calling itself until none remain. Handy, but reach for REDUCE first; it covers most looping needs without recursion’s complexity.

Errors & common pitfalls

#CALC! — the LAMBDA was never called, or got too few arguments. =LAMBDA(x, x*2) alone defines a function and stops; Excel can’t display a function, so it returns #CALC!. Same story for =LAMBDA(x, y, x*y)(5) — two parameters declared, one supplied. Add the missing argument(s) in the trailing parentheses.

#NAME? — older Excel. LAMBDA exists only in Excel for Microsoft 365 and Excel for the web. Excel 2021, 2019, and earlier don’t recognize the name — and workbooks with named LAMBDAs will break for those users too.

#VALUE! — illegal parameter name. Parameter names can’t contain spaces, can’t start with a number, and can’t collide with cell references. R1, XFD2, and my rate all fail; rate1 works.

Pitfall: names are workbook-scoped. A named LAMBDA lives in that workbook’s Name Manager. Open a different file and =FAHRENHEIT(72) is #NAME?. Copy a sheet that uses it into another workbook and the name travels along — that’s the easiest way to share one.

Pitfall: calculation must come last. =LAMBDA(x*2, x) is backwards — parameters first, the calculation as the final argument.

Practice workbook

📊
Download the free LAMBDA practice workbook
Every example on this page, ready to open in Excel — plus practice challenges with answers on a separate tab. No sign-up required.

Frequently asked questions

What does the Excel LAMBDA function do?
LAMBDA creates a reusable custom function from ordinary formula logic. You declare parameter names, write a calculation that uses them, save the LAMBDA under a name in Name Manager, and then call it like a built-in function — no VBA or macros required.
Why does my LAMBDA return #CALC!?
Either you never called it — =LAMBDA(x, x*2) with no trailing (value) — or you supplied fewer arguments than the parameters you declared. Add the arguments in parentheses right after the LAMBDA, or call the named version with a full argument list.
How do I save a LAMBDA so I can reuse it?
Formulas › Name Manager › New. Type the function name (e.g. FAHRENHEIT), put =LAMBDA(f, (f-32)*5/9) in the Refers-to box with no trailing arguments, and add a comment — it becomes the tooltip. Then call =FAHRENHEIT(72) anywhere in that workbook.
Which Excel versions support LAMBDA?
Excel for Microsoft 365 (Windows and Mac) and Excel for the web. Excel 2021, 2019, and all perpetual-license versions show #NAME? — there is no way to backport it.
Can a LAMBDA call itself (recursion)?
Yes — once it has a name, the calculation can reference that name, so a named LAMBDA can call itself until a condition stops it. It’s how pre-REDUCE solutions looped. For most repeat-a-step problems, REDUCE or SCAN is simpler and safer.
LAMBDA vs LET — what's the difference?
LET names values inside a single formula to avoid repeating sub-calculations. LAMBDA names a whole function with parameters you fill in later. They nest beautifully: a LAMBDA whose calculation is a LET is the standard pattern for serious custom functions.

Master functions like this in one day

This page covers one function. Our Excel Formulas and Functions class covers the 30 that matter most — live, hands-on, taught by professionals in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Related functions: MAP · REDUCE · BYROW · BYCOL · MAKEARRAY · SCAN · LET