LET and LAMBDA for Readable Formulas in Excel Workbooks

Open a workbook with a 400-character formula and you will find it written once, tweaked eleven times and understood by nobody. The same expression appears three times inside it, a typo in one of them gave a wrong answer for a quarter and the author has left. LET and LAMBDA exist to stop that. LET lets you name the pieces of a formula, and LAMBDA lets you turn a formula into a function with parameters. Used with restraint, they make workbook logic readable enough to review. This article shows both, with the helper functions that make LAMBDA useful and the places where they do harm. It follows the patterns in Excel formulas data people use every week.

LET: name the pieces

The syntax is pairs of a name and a value, followed by the expression that produces the result:

=LET(name1, value1, name2, value2, ..., calculation)

Take a variance percentage that compares actual spend against budget and guards against a missing budget. Without LET:

=IF(XLOOKUP(A2, Budget!A:A, Budget!B:B, 0) = 0, "",
    (SUMIFS(Sales!D:D, Sales!B:B, A2) - XLOOKUP(A2, Budget!A:A, Budget!B:B, 0))
    / XLOOKUP(A2, Budget!A:A, Budget!B:B, 0))

The lookup is written three times and evaluated three times, and the sum once. With LET:

=LET(
  actual, SUMIFS(Sales!D:D, Sales!B:B, A2),
  budget, XLOOKUP(A2, Budget!A:A, Budget!B:B, 0),
  IF(budget = 0, "", (actual - budget) / budget)
)

Each value is computed once, the intent is visible, and a change to the lookup happens in one place. Line breaks and indentation inside the formula bar are allowed (Alt+Enter, or the expand control on the bar), and they make a large difference to readability.

Rules worth knowing:

• Names are evaluated in order. A later name can use an earlier one; an earlier one cannot use a later one.

• A name exists only inside that LET. It does not clash with workbook names, though a name that looks like a cell reference, such as A1 or R2C3, is not allowed.

• The last argument is always the result, not a pair.

• Up to 126 name and value pairs are allowed, which is far more than readable.

LET also speeds up formulas that repeat expensive work. A FILTER or XLOOKUP over a large range, used in three places, runs three times without it.

LAMBDA: define a function

LAMBDA takes parameter names and a calculation:

=LAMBDA(price, qty, rate, price * qty * (1 + rate))

On its own it does nothing useful, because it needs to be called. You can call it in place by passing values after it:

=LAMBDA(price, qty, rate, price * qty * (1 + rate))(12.5, 4, 0.2)

which returns 60. The useful form gives it a name. In Formulas, Name Manager, New, set the Name to GrossValue and the Refers to box to the LAMBDA expression. Then any cell can use it:

=GrossValue(D2, E2, TaxRate)

The function now exists across the workbook, with one definition. Fix a rounding rule in the name, and every cell that calls it is fixed. This is the nearest thing a workbook has to a shared function, and it is what finally makes long formulas reusable without VBA.

Add a comment in the Name Manager's Comment field describing the parameters and the purpose. Names with prefixes help others find them: fn_GrossValue, so the team sees at a glance that it is a function and not a range.

Combine LET and LAMBDA

A LAMBDA body can use LET for its own steps:

=LAMBDA(region,
  LET(
    rows, FILTER(Orders, Orders[Region] = region, 0),
    total, SUM(INDEX(rows, , 6)),
    n, ROWS(rows),
    IF(n = 0, 0, total / n)
  )
)

Named AvgOrderValue, this gives =AvgOrderValue("North"). The body is short because each step has a name, and the function is easy to test: call it with a few inputs whose answers you know.

Helper functions that take a LAMBDA

A set of array functions accepts a LAMBDA as an argument, applying it across values.

**MAP** runs the function on each element and returns an array of the same shape:

=MAP(A2:A100, LAMBDA(v, IF(v < 0, 0, v)))

**BYROW and BYCOL** apply it to each row or column and return one value per row or column:

=BYROW(B2:E100, LAMBDA(r, MAX(r) - MIN(r)))

**REDUCE** folds an array into one value with an accumulator:

=REDUCE(0, A2:A100, LAMBDA(acc, v, acc + v^2))

**SCAN** is like REDUCE but returns every intermediate value, which gives running totals:

=SCAN(0, D2:D100, LAMBDA(acc, v, acc + v))

That last one is a running total in one cell, with no helper column and no drag-down (running totals in Excel and SQL with window functions compares it with sum() OVER). **MAKEARRAY** builds an array of a given size from a row and column function. These replace loops that used to need VBA or a column of copied formulas.

Debugging

A LAMBDA that returns an unexpected value is harder to debug than a normal formula, since you cannot step into the body easily. Several tactics help:

1. Test with a literal call first: =fn_GrossValue(10, 2, 0.1) and compare with a hand calculation.

2. Return an intermediate: change the last argument of a LET to a name, to see what it holds, then restore it.

3. Use Evaluate Formula on the call site.

4. Keep bodies short. When a LAMBDA runs past six or seven lines, split it into two named functions.

Common errors: #CALC! when an array result is empty (common with FILTER); #VALUE! when an argument has the wrong type; #NAME? when the function is missing in the reader's version or the name is misspelled; and the "wrong number of arguments" message when the call does not supply all parameters.

Recursion

A named LAMBDA can call itself, which allows loops written as recursion. It works, and it is a way to write formulas that nobody else can maintain. The recursion depth is limited, and long recursions are slow. Use REDUCE, SCAN or MAKEARRAY instead where they fit, and do the rest in a query.

Portability and sharing

The largest drawback is compatibility. LET and LAMBDA arrived with Microsoft 365 and Excel 2021. A workbook that uses them shows #NAME? in Excel 2019 and earlier, and Excel for the web and the Mac apps support them in current builds but not in old ones. Google Sheets has LET and LAMBDA as well, with some differences, and LibreOffice has LET in recent versions; check the versions of everyone who opens the file (differences between Sheets and Excel formulas).

Named LAMBDA functions live in the workbook. To share them, copy a sheet that uses them into another workbook (the names come along), keep a template workbook as a library, or move to an add-in. Version them: when you change fn_GrossValue, record the date and the reason in its comment.

When LET and LAMBDA are not the right choice

They are the wrong choice for a file that goes to people with older Excel. They are the wrong choice for logic the whole team or other systems need: a function that exists only inside one workbook is invisible to the database, and the logic is duplicated the moment someone needs it elsewhere. For shared business rules, put the rule in SQL or a query where it is versioned and tested (version controlling the query behind an Excel report). And they are the wrong choice when they hide a mess: a LET with twelve names can make a bad design look tidy. If you need that much, the workbook is probably asking for a different tool. Microsoft documents the functions at LET and LAMBDA.