Assigns names to intermediate calculation results within a single formula, eliminating repeated sub-expressions, improving readability, and making complex formulas debuggable without helper columns.
LET lets you name things inside a formula. Instead of writing the same calculation three times, you write it once, give it a name, and use the name everywhere. Think of it like creating a variable — the same concept you see in every programming language, now inside Excel.
Here’s a real formula. Click each step to understand what it does and why it’s there.
This line does two things: it runs an XLOOKUP to find the amount for the ID in cell A2, and it names the result raw. From this point on, anywhere you write raw inside the formula, Excel substitutes the XLOOKUP result — without running the lookup again.
This line takes the value we named raw and multiplies it by 1.1 (a 10% markup). The result is named adjusted. Notice we’re building on top of a previous step — LET variables can reference earlier LET variables.
This names the number 1000 as threshold. A hardcoded number in a formula is almost always a governance problem — where did 1000 come from? Naming it makes the intent clear: this is the threshold we’re testing against. If the threshold changes, you update it in one place.
The last line of LET is always the output — what the formula actually returns. Here we’re asking: is adjusted greater than threshold? If yes, return adjusted. If no, return 0. Every named variable from above is available here.
The same logic — one formula is defensible, one is not.
=IF(
XLOOKUP(A2,T[ID],T[Amt],0)*1.1
>1000,
XLOOKUP(A2,T[ID],T[Amt],0)*1.1,
0
)
XLOOKUP runs twice. The multiply by 1.1 appears twice. One edit requires two fixes. One typo produces a silent wrong answer.
=LET( raw, XLOOKUP(...), adjusted, raw*1.1, IF(adjusted>1000, adjusted, 0) )
XLOOKUP runs once. The multiply is named. The logic reads like a sentence. One edit, one place.
LET is the preferred pattern for any formula longer than 80 characters. Named variables must describe what they represent, not how they are calculated — adjusted_revenue not B2_times_1point1.
When a formula evaluates the same sub-expression more than once or contains logic that cannot be understood in a single read, use LET to name each component so the formula reads like documented code.
LET requires Excel 365 or Excel 2021 — not available in Excel 2019 or earlier. Variable names cannot conflict with cell addresses or Excel function names. The last expression in LET must not have a trailing comma — this is the most common syntax error.
Each named variable is evaluated once regardless of how many times it appears in the body, making LET faster than repeated sub-expressions on large ranges.
LET is the preferred pattern for any formula longer than 80 characters. Named variables must describe what they represent, not how they are calculated — adjusted_revenue not B2_times_1point1.
LET function · named-variables-in-formula · formula-decomposition
-- Name a single intermediate value =LET( rate, 0.085, base, B2, base * rate ) -- Result: B2 multiplied by 8.5% -- 'rate' is defined once, used once -- but now the formula reads: base times rate
-- Each step builds on the previous =LET( region, "North", total, SUMIFS(D:D, A:A, region), target, Config!Target_North, variance, total - target, IF(variance >= 0, "On track", "Behind") ) -- 'region' feeds into 'total' -- 'total' and 'target' feed into 'variance' -- 'variance' drives the final output
-- LET as the dominant function in a LAMBDA =MAKEARRAY( ROWS(rowsDim), COLUMNS(qtrs), LAMBDA(r, c, LET( rowLabel, INDEX(rowsDim, r), keyQtr, INDEX(qtrs, c), COUNTIFS(dimKey, rowLabel, QuarterTag, keyQtr) ) ) ) -- LET inside LAMBDA names the INDEX results -- so the COUNTIFS reads clearly -- rowLabel and keyQtr used once each, -- but the formula becomes self-documenting