Excel LET function: reusable variables in your formulas

A formula that calls the same lookup four times in a row is telling you something: the workbook outgrew the formula style. The usual fix is a helper column, but helper columns clutter the sheet and break the first time somebody filters or sorts the wrong way. LET handles the same problem without leaving a trail. You name a value once, reuse the name as many times as the formula needs it, and the calculation runs once. Microsoft shipped LET alongside dynamic arrays, and it has been generally available since Excel 2021. If you have Microsoft 365, you have it. Here is where it earns its keep, and where it does not.

How LET reads from the inside out

The signature is short, but the argument order takes a minute to internalize. Each pair is a name and the value bound to it. The final argument — and only the final argument — is the calculation that uses those names.

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

The names act like local variables: they exist only inside this single LET and disappear the moment the formula returns. Microsoft caps the list at 126 name/value pairs, which is more than any human-readable formula will ever need; if you are past 20, you should already be looking at a helper column or a LAMBDA. The official syntax reference is on Microsoft’s LET function page, including the strict rule that the final argument must be a calculation rather than another name.

The smallest useful example

A toy version makes the order click. Read it as: “let tax equal 0.08, then return price * (1 + tax).”

=LET(tax, 0.08, A2 * (1 + tax))

One name, one value, one calculation. Every LET in a real workbook follows the same shape — the body just gets longer.

Naming rules that bite later

Three rules turn into bugs fast if you skip them. First, a name must start with a letter and contain no spaces; underscores are fine. Second, a name cannot look like a cell reference — R1, C5, and A1 are illegal because Excel reads them as cell addresses. Third, and the surprise: a LET name silently shadows a workbook-level named range that uses the same string. If Total is defined in your Name Manager and you also name it Total inside a LET, the local name wins inside that formula and the workbook-level one keeps its value everywhere else.

Names should read as the thing they hold. lookup_range beats r. last_quarter beats q. The whole reason to reach for LET is making the formula readable by the next person who opens the workbook; cryptic names give that benefit back.

• ✓ Starts with a letter, no spaces
• ✓ Does not match a cell-address pattern (no A1, R7, C12)
• ✓ Does not collide with an existing workbook name unless you mean to shadow it
• ✓ Reads as English, not as an abbreviation

A real refactor: before and after

Here is where LET earns the cost of learning it. Say you are calculating tiered commission on a sales sheet where SUMIFS aggregates by salesperson and quarter. Without LET, every conditional has to repeat the same range, the same criteria, and the same lookup — three times for one decision.

=IF(SUMIFS(Sales[Amount],Sales[Rep],A2,Sales[Quarter],"Q1")>50000,
  SUMIFS(Sales[Amount],Sales[Rep],A2,Sales[Quarter],"Q1")*0.1,
  SUMIFS(Sales[Amount],Sales[Rep],A2,Sales[Quarter],"Q1")*0.05)

=LET(
  total, SUMIFS(Sales[Amount],Sales[Rep],A2,Sales[Quarter],"Q1"),
  IF(total>50000, total*0.1, total*0.05))

The “after” version is shorter, faster on every recalc, and easier to edit — when the tier threshold moves from 50,000 to 75,000, you change one number instead of three. For more on multi-criteria conditions in this exact style, the SUMIFS vs COUNTIFS walkthrough covers the underlying aggregations that LET wraps so cleanly.

The data, as a sheet snapshot

For context, here is the source range the formula reads. Commission lives in column D and references the rest of the row.

When LET actually saves recalc time

The marketing pitch for LET talks about speed, but speed only matters when an expensive subexpression repeats. A formula that computes A1+1 twice does not get faster — that addition is free. The savings show up when the repeated piece is one of these heavy operations:

• VLOOKUP, XLOOKUP, or INDEX/MATCH across a long table
• SUMIFS or COUNTIFS over thousands of rows
• FILTER on a dynamic-array source
• Any volatile function: OFFSET, INDIRECT, NOW, RAND

Call any of those three or more times in one formula and LET turns the cost into a single evaluation. For a deeper look at why INDEX/MATCH in particular benefits, our breakdown of INDEX MATCH vs VLOOKUP covers the lookup mechanics that decide which subexpressions are worth naming.

LET with dynamic arrays and FILTER

The combination of LET and dynamic-array functions — FILTER, SORT, UNIQUE, TAKE — is where the new Excel formula model actually clicks. Each step in a pipeline becomes a named value, and the formula reads top to bottom like a short script.

=LET(
  active,   FILTER(Orders, Orders[Status]="Open"),
  ranked,   SORT(active, 4, -1),
  top5,     TAKE(ranked, 5),
  top5)

That formula filters the open orders, sorts the result by column 4 descending, and returns the first 5 rows. Without LET you would either nest the calls — three layers of parentheses no one wants to debug — or spill them across separate cells that clutter the layout. With it, each stage is a name you can reason about on its own. Our roundup of essential Excel formulas covers the dynamic-array building blocks that show up in pipelines like this one.

Debugging when LET breaks

Three errors come up over and over with LET. Each has a specific cause and a specific fix — generic “did you check the parentheses” advice does not apply, because the parentheses are usually fine.

#NAME? almost always means a name was used before it was defined, or a name was misspelled. LET reads strictly left to right, so the order in the formula has to match the order of use. Move the definition earlier in the list.

#CALC! shows up when a named value resolves to an empty array — usually a FILTER that matched nothing in the source range. Wrap the FILTER in IFERROR, or check the result with ROWS before the next stage consumes it.

#VALUE! inside a LET behaves like any other #VALUE!: a type mismatch somewhere. Comment out the final calculation and return one name at a time to find which step produced the bad value. The procedure below is the closest thing Excel has to a print statement.

1. Replace the final calculation with a single name — say total — and see what it returns.
2. If the value is wrong, the bug is in that name’s definition. If it is right, return the next name in the list and repeat.
3. When you find the broken step, the names above it are working baselines you can trust while you patch the bad one.

Where LET stops paying off

Two cases call for a different tool. When the same named pattern shows up across many formulas in the workbook, you want a LAMBDA or a Name Manager entry, not a LET. LET names live inside one formula; copying the same pattern into ten cells means ten copies to keep in sync. And when you are already past 15 or 20 names in one formula, a helper column or a separate staging sheet is usually cheaper to read than another nesting level.