LET and LAMBDA: Naming the Parts of a Formula, and Writing Your Own Functions

Here is a formula that shipped to a real pricing sheet, and it is not the worst one anybody has written this year:

=D2*E2-D2*E2*IF(D2*E2>=5000,0.12,IF(D2*E2>=2500,0.08,IF(D2*E2>=1000,0.04,0)))

It is correct. It returns 2,382.80, which is the right answer. And D2*E2 appears in it five times, which means five things at once: the reader has to work out five times that D2*E2 is the order value, Excel has five places to evaluate it, and the day someone adds a units-per-case column there are five edits to make and four of them are enough.

The fix is not a shorter formula. It is a formula that can say the words "order value" out loud:

=LET(value, D2*E2,
     rate,  IFS(value>=5000, 0.12, value>=2500, 0.08, value>=1000, 0.04, TRUE, 0),
     value - value*rate)

Same answer. One place to change. And a second person can read it.

That is LET: it lets a formula name its own intermediate results. LAMBDA, its partner, goes one step further — it lets a formula take arguments, which turns it into a function you can name and call from anywhere in the workbook, exactly like SUM. Between them they are the largest change to how Excel formulas are written since dynamic arrays, and almost nobody's sheets have caught up.

What you need. LET is in Microsoft 365, Excel 2021 and later, and Excel for the web. LAMBDA and its helpers (MAP, BYROW, SCAN, REDUCE, MAKEARRAY) are in Microsoft 365, Excel 2024 and Excel for the web — not in Excel 2021. Google Sheets has both, plus Named Functions in place of Name Manager. In older Excel, a workbook using either of them opens with _xlfn.LET and _xlfn.LAMBDA sitting in the formulas and #NAME? in the cells — section 13.

1) Two Problems That Look Different and Are Not

Long formulas go wrong in two ways, and people treat them as separate complaints.

The first is repetition. A subexpression that appears more than once — D2*E2, an XLOOKUP you need both the value and the test of, a date difference used in three branches. Every copy is a thing to maintain and a thing to read.

The second is namelessness. Even a formula with no repetition at all becomes unreadable when it is three ideas deep, because none of the ideas has a name. =IF(TODAY()-C2>30, (D2*E2)*0.02, 0) is arithmetic; late fee = 2% of order value when the invoice is over 30 days old is a sentence. The formula contains the sentence and refuses to say it.

LET fixes both with one move, and the reason is that both problems are the same problem: a value that matters to the reader has nowhere to live except inside the expression that produced it. Give it a name and the repetition collapses into a reference, and the sentence appears.

The alternative people reach for first is helper columns, and helper columns are genuinely good — they are visible, they are auditable, and every reviewer can see them. LET is what you use when the intermediate is not worth a column: too many of them, or a sheet someone else designed and you cannot widen, or a value that only makes sense inside this one calculation. The two are not rivals. A sheet with eleven helper columns nobody can name is as bad as a formula with eleven nested parentheses.

2) The Syntax, and the Odd Number

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

Pairs, then one final argument that is the actual answer. Which produces the rule that catches everyone once:

The argument count is always odd. Three, five, seven, nine. An even count means you have written a name with no value, or — far more often — you have forgotten the calculation at the end and Excel is looking at your last name/value pair as though it were the answer. Excel refuses the entry rather than guessing.

Four more rules, all of which matter later:

1. Names are declared left to right, and a name can only see the names before it. LET(b, a*2, a, 10, b) fails; LET(a, 10, b, a*2, b) is the same idea in the right order.
2. Each name is calculated once, and reused wherever it appears. This is a promise about evaluation, not just about typing, and section 7 is what it buys you.
3. The names exist only inside this formula. Nothing appears in Name Manager, nothing is visible to the cell next door, and two formulas can both use rate for different things without a conflict.
4. Up to 126 name/value pairs, which you will never reach, and should treat as a warning if you approach.

The last argument does not have to use every name — a LET whose final calculation ignores half its declarations is legal, and is usually a formula somebody edited badly.

3) Rewriting the Monster

🎯 Scenario: Seven orders, a tiered discount, and a net value column that has to be right by Thursday.

The grid below is the sheet. Units in D, unit price in E, and F is empty because this section fills it. The discount rules are the ones every sales sheet has: 4% from 1,000, 8% from 2,500, 12% from 5,000.

Written as one expression it is the formula at the top of this article — five D2*E2s. Written with LET, and laid out with Alt+Enter inside the formula bar, it is a five-line program:

=LET(
  value,    D2*E2,
  rate,     IFS(value>=5000, 0.12, value>=2500, 0.08, value>=1000, 0.04, TRUE, 0),
  discount, ROUND(value*rate, 2),
  net,      value - discount,
  net
)

Fill F2:F8 and the column reads:

Three details in that formula are deliberate and none of them is about LET.

ROUND sits on the discount, not on the net. Round the net instead and discount plus net stops equalling order value by a cent here and there, which is the kind of thing a finance system rejects a file for. Round the number that is derived by multiplication, then subtract.

IFS ends with TRUE, 0. Without it, an order under 1,000 returns #N/A rather than a zero discount, because IFS has no else-branch of its own.

The final argument is just net. It could have been value - discount directly and saved a line. Naming it anyway means the last line of the formula reads like the column heading, and — section 6 — it gives you the one-character edit that turns this formula into its own debugger.

4) Names Excel Will and Won't Accept

LET names follow the same rules as defined names, and the rules are stricter than people expect.

Names are case-insensitive: Rate and rate are the same name, and declaring both is an error rather than two variables. Excel remembers the capitalisation you typed and does not normalise it, so a formula can look like it has two names when it has one.

Then the trap that has no error message at all. A LET name shadows a workbook defined name of the same word, inside that formula only. If Rate is a defined name pointing at Settings!$B$4 and you write LET(rate, 0.08, ...), every rate in that formula is 0.08 and the setting is ignored — silently, correctly, and exactly as designed. It is a good feature and a bad surprise. The habit that avoids it: name your LET values after what they are in this formula (value, rate, net), and keep workbook-level names in a shape you would not casually retype (Tax_Rate_Standard, cfg.ShipCutoff).

Long names are not a virtue either. Inside a five-line formula, v is too short to help and order_value_before_discount pushes the line past where anyone can read it. The names in section 3 are the length that works: one word, lowercase, the thing you would say out loud.

5) Names That Build on Names

The left-to-right rule is not a limitation to work around. It is the feature — it means a LET reads top to bottom like the steps of the calculation, and each step can lean on the one above.

🎯 Scenario: Sales want the commission too. It is 3% of net value, but never less than 25 per order, and never more than 250.

=LET(
  value,      D2*E2,
  rate,       IFS(value>=5000, 0.12, value>=2500, 0.08, value>=1000, 0.04, TRUE, 0),
  discount,   ROUND(value*rate, 2),
  net,        value - discount,
  raw,        net * 0.03,
  commission, MEDIAN(25, raw, 250),
  net - commission
)

Six names, each one built from the ones above it, and the whole thing is still one cell. Row 2: net 2,382.80, raw commission 71.4840, no floor or cap applied, so the cell holds 2,311.316 and shows 2,311.32. Row 6 (SO-1045, the 960 order): net 960.00, raw commission 28.80, again comfortably inside the band.

MEDIAN(25, raw, 250) is the clamp trick and it is worth stealing: the middle of floor, value, cap is the value squeezed into the band, with no IF at all. MAX(25, MIN(250, raw)) is the same thing and reads worse.

Two things this shape gives you beyond readability.

A changed rule has one home. Commission moves to 3.5%? One number, one line, and nothing else in the formula knows or cares. Compare that with the single-expression version, where net is spelled out twice — once for the commission and once for the subtraction — and a careless edit changes one of them.

The steps are the audit trail. When someone asks in a meeting why SO-1047 came out at 2,324.84, the formula answers in order: order value 2,527.00, tier 8% because it cleared 2,500, discount 202.16, net 2,324.84. That is the whole conversation, and it is sitting in the cell.

6) LET as Its Own Debugger

The reason to name the last step rather than inline it: change the final argument to any name and the formula returns that name's value.

=LET(value, D2*E2, rate, IFS(...), discount, ROUND(value*rate,2), net, value-discount, rate)

That returns 0.08. Change it back to net and you have your formula again. One word, at the end, and the cell shows you any intermediate you like — no helper column, no F9, no risk of half-evaluating something and pressing Enter by mistake.

The three other tools, and when each beats it:

F9 deserves its warning. It replaces the highlighted text with its value in the formula bar, and pressing Enter commits that — permanently converting a live subexpression into a hardcoded number. Esc undoes it; Enter does not. Every spreadsheet has at least one constant that got there this way.

The LET swap has a specific advantage over both: it shows the value of the name as this formula uses it, including the shadowing from section 4. If a workbook name is being overridden, F9 on a fragment can show you the wrong thing while the swap shows you the truth.

7) What "Calculated Once" Actually Buys

Each name in a LET is evaluated once, however many times it appears. The consequences run in two directions.

Speed, sometimes. Take a 20,000-row column whose formula does the same MATCH three times. As one expression that is 60,000 lookups per recalculation; wrapped in a LET it is 20,000. On a large sheet that is the difference between a pause and a coffee.

The honest caveat: Excel's calculation engine already recognises some repeated subexpressions and caches them, so the improvement is rarely the clean 3× the arithmetic suggests, and on small ranges it is unmeasurable. Rewrite for readability, take the speed as a bonus, and measure before you tell anyone a number. If a workbook is genuinely slow, the cause is far more likely to be full-column references, volatile functions, or an array that is 400 times bigger than the data in it.

Semantics, always. This one is not an optimisation, it is a change in meaning:

=RAND() & " / " & RAND()               →  0.4712... / 0.9033...     two different numbers
=LET(r, RAND(), r & " / " & r)         →  0.4712... / 0.4712...     one number, twice

Both are doing exactly what they say. NOW(), TODAY(), RANDBETWEEN(), OFFSET() and INDIRECT() all behave this way inside a LET — named once, sampled once. Usually that is what you wanted and did not know how to ask for: a formula that stamps the same timestamp in three places, or draws one random pick and uses it consistently. Occasionally it silently removes variation you were relying on, and a sampling sheet that suddenly returns identical values down a row is the symptom.

8) LAMBDA: When the Formula Should Take Arguments

LET names values inside one formula. The next problem is a formula you want in many places: the discount ladder from section 3 is a business rule, and it now lives in seven cells on this sheet and probably eleven cells on three others. Change the tiers and you are hunting.

LAMBDA turns that rule into a function.

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

Parameters first, calculation last — the same shape as LET, except the names get their values from whoever calls it rather than from the formula itself.

Type one straight into a cell and Excel returns #CALC!. That is not a bug: you have defined a function and never called it, and #CALC! is Excel saying so. Which leads to the trick that makes LAMBDA learnable — call it in place by putting the arguments in brackets afterwards:

=LAMBDA(value, IFS(value>=5000, 0.12, value>=2500, 0.08, value>=1000, 0.04, TRUE, 0))(2527)

Result: 0.08. The (2527) on the end is the call. Test every LAMBDA this way, in a scratch cell, with the awkward values — 2,499 and 2,500 and 999 — before you give it a name. A LAMBDA that is wrong in a cell is a typo; a LAMBDA that is wrong in Name Manager is wrong in forty places at once.

9) Naming It: Four Fields and a Tooltip

Formulas → Define Name, or Ctrl+F3 for the full Name Manager.

Now =DiscountRate(D2*E2) works anywhere in the workbook, autocompletes in the formula bar, and shows your comment while it does. There is one edit for the whole business rule and it is in a dialog box with a description attached — which is more documentation than most workbooks have anywhere.

The Comment field is the part everyone skips. It is the only place in Excel where a custom function can explain itself to the next person, it costs one sentence, and without it your colleague sees a function name they have never heard of and no way to find out what it wants.

Build the second one on top of the first, because a LAMBDA can call another LAMBDA and can use LET inside itself:

NetValue  =LAMBDA(units, price,
             LET(value, units*price,
                 discount, ROUND(value * DiscountRate(value), 2),
                 value - discount))

And F2 becomes =NetValue(D2, E2). Row 2 returns 2,382.80, exactly as before — but the sheet now says what it is doing, and the three cells that need the rate rather than the net can still get it from DiscountRate without duplicating the ladder.

One practical note on editing: the "Refers to" box in Name Manager behaves like a formula bar, which means arrow keys start inserting cell references instead of moving the cursor. Press F2 to switch that box into edit mode first. It is a small thing that makes people give up on Name Manager entirely.

10) The Four Errors, and What Each One Means

LAMBDA fails in a small number of ways and each error is specific enough to diagnose from the cell.

The one that is genuinely confusing is #VALUE!, because Excel does not tell you which function got the wrong count, and a nested call three levels down reports at the top. When a working LAMBDA starts returning #VALUE! after an edit, the first thing to check is whether you added a parameter to the definition and left the call sites alone.

Optional parameters exist, and they need ISOMITTED to be useful:

NetValue  =LAMBDA(units, price, [round_to],
             LET(dp, IF(ISOMITTED(round_to), 2, round_to),
                 value, units*price,
                 value - ROUND(value * DiscountRate(value), dp)))

Square brackets in the parameter list mark it optional; ISOMITTED tests whether the caller supplied it. Without ISOMITTED an omitted parameter arrives as an error value and poisons the whole calculation, which is a confusing way to find out you needed it.

11) The Helpers: One Formula for a Whole Column

A named LAMBDA is useful on its own. It becomes something else when you hand it to a function that applies it repeatedly — the six helpers that shipped alongside it.

The whole net-value column, as one formula in F2, spilling seven rows:

=MAP(D2:D8, E2:E8, LAMBDA(u, p, NetValue(u, p)))

Result: 2,382.80 down to 2,324.84 — the same seven numbers as section 3, from a single cell, with no fill-down to get out of step and no half-filled column when someone adds row 9. Point it at a Table column instead of D2:D8 and the range grows on its own.

The grand total, without a helper column at all:

=SUM(MAP(D2:D8, E2:E8, LAMBDA(u, p, NetValue(u, p))))

Result: 19,947.64.

SCAN earns its place on running totals, which are the classic reason people write $B$2:B2 and then discover it breaks when the table is sorted:

=SCAN(0, F2:F8, LAMBDA(acc, v, acc + v))

Result: 2,382.80 / 5,032.40 / 7,422.80 / 11,858.00 / 12,818.00 / 17,622.80 / 19,947.64.

Two cautions before you convert everything. A spilled column cannot be edited row by row, so the one order that needs a manual override now needs a rule instead — which is usually an improvement and occasionally a fight. And MAP over tens of thousands of rows is slower than the same logic filled down, sometimes markedly, because each element is a separate call rather than one vectorised pass. The elegance is real; so is the cost.

12) Recursion, Briefly and Carefully

A named LAMBDA can call itself. This is the capability that makes it a real programming language rather than a macro, and it is also the one that will lock up your workbook if you are careless.

The rule for every recursive function is the same: the base case comes first, and it must be reachable.

CleanDigits  =LAMBDA(text,
                IF(LEN(text)=0, "",
                  LET(first, LEFT(text, 1),
                      rest,  MID(text, 2, LEN(text)),
                      IF(ISNUMBER(first*1), first, "") & CleanDigits(rest))))

=CleanDigits("SO-1041") returns 1041. It takes the first character, keeps it if it is a digit, and hands the rest of the string back to itself — and it stops because rest is shorter every time and the empty string returns immediately.

Delete the LEN(text)=0 line and the same function calls itself forever. Excel does not have a friendly message for this: you get #NUM! if you are lucky, a long freeze and a memory spike if you are not. Test recursive functions on short inputs first, and save before you test.

Recursion depth is finite and lower than people expect — a few thousand levels, depending on how much the function carries with it, and each level costs memory. For anything with a known shape, REDUCE or SCAN is faster and cannot run away. Keep recursion for genuinely unbounded problems: walking a string of unknown length, a hierarchy of unknown depth, an iteration that stops when a tolerance is met.

And check whether your build has caught up before writing one of these. CleanDigits above is a fine teaching example and a poor answer in a modern 365 build, where =REGEXEXTRACT("SO-1041", "\d+") does the same job in one call. Recursion is a tool for problems Excel has no function for — the list gets shorter every year.

13) Where a LAMBDA Lives, and How It Gets Lost

This is the part that decides whether custom functions survive contact with your colleagues.

A named LAMBDA is stored in the workbook's defined names. It is not stored in the formula that calls it, not in your Excel installation, and not in your account. Which produces three failures worth knowing before they happen:

• Copy a cell to another workbook and the formula arrives without the function. =NetValue(D2,E2) becomes #NAME? on the other side, and there is nothing in the cell to tell the recipient what was missing.
• Copy the whole sheet and the names come with it, because sheet copies carry their dependencies. This is the cheapest way to move custom functions between workbooks and almost nobody knows it.
• Send the file to Excel 2019 and every LET and LAMBDA shows as _xlfn.LET and _xlfn.LAMBDA with #NAME? in the cell. The formula is preserved perfectly and cannot run. If the file has to open on old builds, this is not a formatting problem you can style around — the calculation is gone.

There is no built-in library, no import, and no version history for defined names. Two habits cover most of the risk. Keep a plain sheet in the workbook listing each custom function, its arguments and one worked example — Name Manager holds the definition but shows it in a box three lines tall, and the comment field does not survive being read in a hurry. And if you are building more than a handful, install Excel Labs from the add-ins store: its Advanced Formula Environment gives you a real editor, with line breaks, indentation and comments that persist, instead of a dialog box.

The wider point is worth saying plainly. A LAMBDA is code, in a place with no source control, no tests and no review. That is not a reason to avoid it — a business rule written once and named is safer than the same rule pasted into forty cells. It is a reason to treat the workbook that holds your functions as something more than a spreadsheet.

14) The Cliff Nobody Asked LET About

One last thing about section 3's ladder, because it is the sort of thing you only notice once the formula is readable enough to think about.

The tiers jump. An order of 2,499 gets 4% and nets 2,399.04. An order of 2,500 gets 8% and nets 2,300.00. The larger order is worth 99.04 less to you, and it stays worse until the order reaches 2,607.65, where 8% of a bigger number finally catches up.

SO-1043 and SO-1047 in the table sit either side of that line: 2,490 nets 2,390.40, and 2,527 — thirty-seven of extra business — nets 2,324.84. Sixty-five and a half less, for selling more.

LET did not cause that and cannot fix it. The fix is a marginal ladder, where each rate applies only to the slice above its threshold, the same way income tax works:

=LET(
  v, D2*E2,
  d, MEDIAN(v-1000, 0, 1500)*0.04
   + MEDIAN(v-2500, 0, 2500)*0.08
   + MAX(v-5000, 0)*0.12,
  v - ROUND(d, 2)
)

Each MEDIAN is a slice clamped to its own band, and the cliff is gone: 2,499 nets 2,439.04, 2,500 nets 2,440.00, and no order is ever worth less than a smaller one.

Which is exactly the kind of formula that would be unreadable without a name in front of it, and is the reason this article ends here rather than with the tiers. The point of naming things is not tidiness. It is that a formula you can read is a formula whose business logic you can argue with — and the cliff was in that sheet for two years while everyone was busy counting brackets.

15) Mini Exercises

Copy the grid into a blank sheet starting at A1. Each answer is one formula or one dialog box.

1. Name the repeat. Write the section 3 LET in F2 and fill down to F8. Then say how many times D2*E2 appears in your formula, and how many times it appeared in the version at the top of this article.
2. Break it on purpose. Delete the final net from your formula so the argument count becomes even. Write down exactly what Excel does, and why an even count is always a mistake.
3. Debug by swap. Without adding a single cell, make F2 show the discount rate it used, then the discount amount, then put it back. Say which character you changed each time.
4. Order matters. Rewrite the formula with rate declared before value and describe what happens. Then explain the rule in one sentence.
5. Clamp it. Add the commission from section 5 and report the after-commission figure for SO-1044 and SO-1045. Say whether either one hits the floor or the cap, and then give the largest order value that would hit the 25 floor.
6. Make it a function. Define DiscountRate in Name Manager with workbook scope and a comment. Test it at 999, 1,000, 2,499, 2,500 and 5,000 before you use it anywhere, and state which three of those five you would have got wrong with > instead of >=.
7. One cell, seven answers. Replace F2:F8 with a single MAP over D2:D8 and E2:E8. Then add an eighth order in row 9 and say what you have to do to include it — and what you would have had to do with a filled-down column.
8. The cliff. Compute the net value of an order worth exactly 2,499 and one worth exactly 2,500 under the section 3 ladder. Then do both again under the marginal ladder in section 14, and say in one line which version you would defend to a customer.

Summary

LET names values inside a formula: pairs of name and value, then the calculation, always an odd number of arguments, each name evaluated once and visible only to the names after it. It fixes repetition and namelessness together, because they were the same problem.

LAMBDA gives a formula parameters, which makes it a function. Test it in a cell with (args) on the end, then put it in Name Manager with workbook scope and a comment, and hand it to MAP, BYROW, SCAN or REDUCE when you want it applied to a whole range from one cell.

The specifics that save the most time: an even argument count means a missing final calculation; a LET name silently shadows a workbook name of the same word; swapping the last argument for a name turns any LET into its own debugger; #CALC! means an uncalled LAMBDA and #VALUE! means the wrong number of arguments; and a custom function lives in that workbook — copy the sheet, not the cell.

And the real return on all of it is section 14. The formula at the top of this article was correct for two years, and nobody could see that the rule it implemented cost the business money on every order just over 2,500. Formulas you can read are formulas you can question. That is worth more than the brackets you save.