Excel LAMBDA Function

LAMBDA lets you write your own custom Excel function using a formula, without VBA, macros, or add-ins. Once you name it, that function behaves like any built-in Excel function: type its name, supply arguments, get a result. The catch is that a LAMBDA only becomes reusable across your workbook after you register it in Name Manager. Skip that step, and it only works as a one-time inline calculation.

Available in: Excel 365 and Excel 2024. Not available in Excel 2021, Excel 2019, or earlier.

Syntax

=LAMBDA([parameter1, parameter2, ...,] calculation)

Parameter	Required	Description
`parameter1, parameter2, ...`	No	Placeholder names for the values your function will accept. You can define zero, one, or up to 253 of them.
`calculation`	Yes	The formula that runs using those parameters. This is always the last argument, and it's the only one that's required.

⚠

Typing a LAMBDA directly into a cell doesn't make it reusable. It only becomes a callable function once you register it in Name Manager under a name. Until then, it just evaluates once, in that one cell.

Basic Example

Say you're calculating the net price for orders in a sales tracker, applying a discount rate and a tax rate to a base price.

=LAMBDA(price, discount, tax, price * (1 - discount) * (1 + tax))(B2, C2, D2)

// price    = B2, the base list price
// discount = C2, the discount rate as a decimal
// tax      = D2, the tax rate as a decimal

This calls the LAMBDA immediately by appending a second set of parentheses with the actual values. B2 becomes price, C2 becomes discount, D2 becomes tax, and the calculation runs once. Nothing gets saved. Type = in another cell and this function won't show up.

To reuse the logic everywhere, name it. Open Formulas > Name Manager > New, call it NETPRICE, and paste just the LAMBDA definition into the Refers To box:

=LAMBDA(price, discount, tax, price * (1 - discount) * (1 + tax))

Now =NETPRICE(B2, C2, D2) works in any cell in the workbook, exactly like a built-in function.

How LAMBDA Works

Two ways to call a LAMBDA

You can invoke a LAMBDA inline, by adding a second parentheses with arguments right after the definition, or you can call it by name, once it's registered in Name Manager. Inline invocation is useful for testing a calculation before you commit to naming it. Named invocation is what makes it a real function you can drop into any formula.

Handling optional parameters with ISOMITTED

LAMBDA has no native syntax for default parameter values, but ISOMITTED gets you there. It checks whether an argument was left out of the call and lets you branch accordingly:

=LAMBDA(price, nearest,
  IF(ISOMITTED(nearest), MROUND(price, 0.05), MROUND(price, nearest))
)

Call ROUNDPRICE(9.97) and it rounds to the nearest 0.05 by default. Call ROUNDPRICE(9.97, 0.10) and it rounds to the nearest dime instead. Without ISOMITTED, omitting the second argument would just throw #VALUE!.

Scope: LAMBDA reads live values, not snapshots

Anything a LAMBDA references outside its own parameters, a named range, a table, another named LAMBDA, gets re-evaluated live every time the function runs. It's not frozen at the moment you defined it. Change the data in that range and every call to the LAMBDA reflects the new values on the next recalculation, the same as any other formula.

Naming and scope in Name Manager

Names default to workbook scope, meaning NETPRICE works from any sheet. You can restrict a name to a single sheet from the scope dropdown in Name Manager, which is worth doing for a helper function you built for one specific report and don't want cluttering the global name list.

Recursion requires the LAMBDA's own name

A LAMBDA can call itself, which is how Excel handles recursion without VBA. This only works once the function is named, because the self-reference points at that name. You can't test recursion by invoking a LAMBDA inline; the name it's calling doesn't exist yet at that point. You have to name it first, then call it.

Common Use Cases

Building a reusable business formula

A shipping calculator that charges a flat fee based on order weight brackets, callable from anywhere in a logistics tracker.

=LAMBDA(weight,
  IF(weight <= 1, 4.99,
  IF(weight <= 5, 8.99,
  IF(weight <= 20, 14.99, 24.99))))(F2)

// weight = F2, order weight in pounds

Named as SHIPFEE, it becomes =SHIPFEE(F2) in every row of the order sheet.

Applying custom logic across an array with MAP

Rounding every price in a column to the nearest nickel without a helper column.

=MAP(prices, LAMBDA(p, MROUND(p, 0.05)))

// prices = named range of raw list prices
// p      = each individual price MAP passes in, one at a time

MAP applies the LAMBDA to every value in prices and spills the results as an array, same size as the input.

Recursive LAMBDA for declining-balance depreciation

Calculating remaining book value after a set number of years of declining-balance depreciation, with no year-by-year helper columns. Named DEPR in Name Manager:

=LAMBDA(cost, rate, years,
  IF(years <= 0, cost, DEPR(cost * (1 - rate), rate, years - 1))
)

Call =DEPR(50000, 0.2, 4) and it returns the book value of a $50,000 asset after four years of 20% declining-balance depreciation. Each call reduces years by one until it hits zero, then unwinds and returns the result. This only works because DEPR exists as a registered name; the formula inside is calling itself by that name.

Sharing a LAMBDA library across workbooks

Named LAMBDAs are workbook-scoped. There's no live external-reference syntax that calls a LAMBDA defined in a different file, the way you can reference a cell in another workbook. Three real options exist instead:

Copy a sheet, or a cell that calls the LAMBDA, from the source workbook into the destination workbook. This imports the underlying Name Manager entry along with it.
Store shared LAMBDAs in PERSONAL.xlsb, the hidden workbook that stays open in the background, and call them as =PERSONAL.xlsb!NETPRICE(B2, C2, D2). This only works on the machine where PERSONAL.xlsb has been set up.
Package a set of LAMBDAs as an .xlam add-in and load it. This makes them available in every workbook opened on that installation, including files shared with someone else who has the add-in installed.

None of these travel automatically with a workbook the way an ordinary formula does. Send a file that calls NETPRICE to someone without the name defined on their end, and it returns #NAME?.

Handling Errors

LAMBDA doesn't have its own unique error type. It propagates whatever error the inner calculation produces, plus a couple of failure modes specific to how LAMBDA gets called.

Common causes:

#CALC!: a recursive LAMBDA with no base case. Without a condition that stops the recursion, Excel keeps calling the function until it detects a circular loop.
#NAME?: the function isn't recognized. Usually it means you're calling a named LAMBDA in a workbook where it was never defined, or the name is misspelled.
#VALUE! or another propagated error: the calculation received the wrong number of arguments, or one of the inputs can't be processed, same as any other formula.

=IFERROR(NETPRICE(B2, C2, D2), "Check inputs")

✓

Test a recursive LAMBDA with small inputs first, years = 1 or 2, before running it against real data. An infinite loop from a missing base case can lock up Excel's calculation engine long enough that Ctrl+Break becomes your only way out.

Notes & Gotchas

What is LAMBDA function in Excel?

LAMBDA is an Excel function that lets you define your own custom function using a formula instead of VBA. You give it parameter names and a calculation, and once named, it works exactly like a built-in function such as SUM or VLOOKUP.

How do you build and use a custom LAMBDA function in Excel?

Write the LAMBDA with your parameters and calculation, then test it by invoking it inline with real values before naming it. Skipping this step is common, and it means the first bug you catch is after you've already registered the function, which is a more annoying place to debug from. Once it works, go to Formulas > Name Manager > New, give the function a name, and paste the LAMBDA definition, parameters and calculation only, into the Refers To box. Click OK, and the name is callable from any cell in the workbook, exactly like =NETPRICE(B2, C2, D2).

What are practical examples of the Excel LAMBDA function?

Common ones include reusable pricing formulas (NETPRICE), tiered fee calculators (SHIPFEE), custom rounding logic applied across a column with MAP, and recursive calculations like declining-balance depreciation that would otherwise need a helper column per period. Anywhere you'd copy the same multi-step formula across a workbook is a candidate for a LAMBDA.

Can I document what a LAMBDA's parameters do for other users?

Not with a live tooltip while someone is typing the formula. Excel does let you add a description in the Comment field of Name Manager, and that description appears in the Insert Function dialog and Formula Builder, which helps anyone who opens that dialog to look up the function. It's a partial fix, not full IntelliSense.

Why does a named LAMBDA return #NAME? after I copy it to another workbook?

Copying a cell that calls NETPRICE doesn't bring the name definition with it. The name has to exist in the destination workbook's Name Manager first. Recreate it there, copy over a sheet that contains the definition, or use PERSONAL.xlsb or an .xlam add-in instead of copying the calling formula alone.

Does LAMBDA support default parameter values?

Not natively. There's no syntax for LAMBDA(price, nearest = 0.05, ...). The workaround is ISOMITTED, which checks whether an argument was left out of the call and lets your formula branch to a default value when it was.

Does LAMBDA work with dynamic arrays?

Yes. A LAMBDA parameter can accept an array, and its calculation can return one, which spills just like any other array formula. This is exactly how MAP, BYROW, BYCOL, and REDUCE pass a LAMBDA across multiple values instead of one.

Related Functions

Function	Use this when...
`LET`	You need a named calculation once, inside a single formula, without making it reusable elsewhere.