LET and LAMBDA: formulas you can read

There is a moment in every improver’s life when a formula crosses the line from long to illegible — usually the third time the same XLOOKUP appears inside its own error check. For decades the only options were “live with it” or “helper columns”. Both are honourable. But modern Excel added a third: name things.

LET names the steps inside one formula. LAMBDA names the whole formula so anyone can reuse it. Together they’re the difference between formulas you decode and formulas you read — and they’re easier than their reputations suggest.

LET: name your steps

The classic mess — a lookup you need to test, trim and reuse:

=IF(XLOOKUP(TRIM(A2),Prices[SKU],Prices[GBP])="", 0,
    XLOOKUP(TRIM(A2),Prices[SKU],Prices[GBP]) * (1+VAT))

The same lookup, written twice; the logic, buried. With LET, you declare name, value pairs, then one final expression that uses them:

=LET(
  sku,    TRIM(A2),
  price,  XLOOKUP(sku, Prices[SKU], Prices[GBP]),
  IF(price="", 0, price * (1+VAT))
)

Read it top to bottom: here’s the cleaned key; here’s its price; if there’s no price, zero, otherwise price plus VAT. The steps have names, the lookup runs once (faster, on big sheets noticeably), and next February you’ll understand it in one pass.

That’s the entire function. Pairs of name-then-value, final answer last. It’s the naming instinct — vat_rate beating $E$1 — applied inside the formula.

LAMBDA: name the whole recipe

LET tidies one cell. LAMBDA goes further: it wraps logic into a function with your name on it, usable anywhere in the workbook.

Say three different sheets compute a runway: =IF(floor=0, 0, ROUND(cash/floor, 1)). Wrap it once:

=LAMBDA(cash, floor, IF(floor=0, 0, ROUND(cash/floor, 1)))

Then Formulas → Name Manager → New, name it RUNWAY, paste that as the definition — and every sheet can now write:

=RUNWAY(B2, C2)

Before and after LAMBDA. The sprawl doesn't just look better — it has one home, one fix, and a name a colleague can search for.

The payoff is the same one software people discovered decades ago: logic that exists once can only be wrong once. When the VAT treatment changes, you edit one definition and every cell using PRICE_INC_VAT is correct — versus hunting seventy pasted copies, of which you’ll find sixty-eight.

The workflow that makes LAMBDA humane

Nobody writes a LAMBDA cold inside the cramped Name Manager box. The civilised route:

Build the logic as a LET in a spare cell, with test inputs pointing at real cells. Debug it where you can see it.
When it’s right, wrap it: change the outer LET variables that were inputs into LAMBDA parameters.
Test in-sheet — a LAMBDA can be called inline while you check it: =LAMBDA(x,y,…)(B2,C2).
Then paste into Name Manager, name it in CAPS (so it reads like a built-in), and add the comment field saying what it does.

Delete the scaffolding cell and the workbook now has a custom function with no VBA, no macros, no security warnings — it travels inside the file like any formula.

Where the boundaries are

Honest edges, as ever. If a LET has a dozen steps, the logic probably wants to be columns — helper columns are still the most auditable structure in Excel, because you can see every intermediate value (the clever-cell rule hasn’t been repealed). If the job is reshaping whole datasets, it wants Power Query, not a heroic formula. And LAMBDA’s companion functions — MAP, BYROW, REDUCE — are genuinely powerful and genuinely a rabbit hole; meet them after the basics have paid rent.

LET and LAMBDA sit at the far end of the path, but the idea they carry is the whole path in miniature: every stage — Tables, named ranges, readable lookups — has been about replacing cryptic with named. This is just the same kindness, extended to your logic.

Name the steps. Then name the recipe. Future-you sends thanks.