Excel LAMBDA Function

LAMBDA lets you define your own reusable Excel function from a formula — no VBA required. You write =LAMBDA(parameters, calculation), save it under a name in Name Manager, and then call it like any built-in function anywhere in the workbook.

What Is the LAMBDA Syntax?

LAMBDA takes up to 253 parameter names followed by one calculation that uses them; to test it inline, append the arguments in parentheses.

=LAMBDA(x, y, x * y)
' On its own this returns #CALC! — it's a function with no inputs

=LAMBDA(x, y, x * y)(4, 5)
' 20 — appending (4, 5) calls it immediately for testing

How Do You Save a LAMBDA in Name Manager?

Go to Formulas > Name Manager > New, type the function name, and paste the LAMBDA (without trailing arguments) into "Refers to" — from then on the name works like a native function.

' Name:      GROSSMARGIN
' Refers to: =LAMBDA(revenue, cost, (revenue - cost) / revenue)

' Use anywhere in the workbook:
=GROSSMARGIN(B2, C2)     ' 0.35

This works like named ranges, except the name stores a function instead of a reference. Put the logic in one place and every cell that calls it updates when you edit the definition.

How Do You Use LAMBDA With MAP and BYROW?

Helper functions like MAP, BYROW, and BYCOL take a LAMBDA and apply it across an array, replacing helper columns with a single spilling formula.

' MAP: apply to each cell of one or more arrays
=MAP(B2:B10, C2:C10, LAMBDA(rev, cost, (rev-cost)/rev))
' Spills 9 margin values

' BYROW: apply to each ROW, one result per row
=BYROW(B2:E10, LAMBDA(row, MAX(row) - MIN(row)))
' Range (max minus min) of every row

' REDUCE: accumulate across an array
=REDUCE(0, B2:B10, LAMBDA(acc, v, acc + v^2))
' Sum of squares

These pair naturally with dynamic arrays: the LAMBDA runs once per element and the results spill.

Can a LAMBDA Call Itself (Recursion)?

Yes — a named LAMBDA can reference its own name, which enables loops that plain formulas can't do, like stripping a list of characters from text.

' Name: STRIPCHARS
' Refers to:
=LAMBDA(text, chars,
   IF(chars = "", text,
      STRIPCHARS(SUBSTITUTE(text, LEFT(chars, 1), ""),
                 MID(chars, 2, 999))))

=STRIPCHARS("a-1_b-2", "-_")   ' "a1b2"

Keep recursion shallow; deep recursion is slow and can hit calculation limits. For most repetition, REDUCE is the better tool.

Common Pitfalls

• #CALC! when called bare: a LAMBDA with no arguments supplied returns #CALC!. Add (args) to invoke it.
• Names are workbook-scoped: your custom function doesn't travel to other files. Copy the Name Manager entry, or use the Advanced Formula Environment add-in to manage them.
• Parameter names can't look like cell references: x and revenue are fine; A1 or rev1 will collide.
• Version support: LAMBDA and its helpers (MAP, BYROW, REDUCE, SCAN) require Excel 365 — older versions show #NAME?.