Excel LET Function

LET assigns a name to a value or a calculation inside a formula, then lets you reuse that name anywhere else in the same formula. It returns whatever the final argument evaluates to. LET calculates a repeated expression once instead of every time it appears, and it turns a wall of nested lookups into something you can actually follow six months later. That's the real payoff: faster recalculation, and formulas you can still read after you've forgotten how they work.

Available in: Excel 365, Excel 2021. Not available in Excel 2019 or earlier.

Syntax

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

Parameter	Required	Description
name1	Yes	The name you're assigning. Must start with a letter, can't contain spaces or punctuation (underscores are fine), and can't look like a cell reference.
value1	Yes	The value, cell reference, or expression assigned to name1. Can reference an earlier name in the same LET.
name2, value2, ...	No	Additional name/value pairs. LET accepts up to 126 pairs total, but only the first pair is mandatory.
calculation	Yes	The final argument. This is what LET actually returns, and it must be the last thing in the function. Every name you defined is available here.

Basic Example

You're building a commission tracker and need to look up a rep's sales total, then calculate their tier bonus based on that same number. Without LET, the lookup gets typed twice:

=LET(
    sales_total, XLOOKUP(A2, reps[Name], reps[Sales]),
    IF(sales_total > 50000, sales_total * 0.1, sales_total * 0.05)
)

// sales_total = the XLOOKUP result, calculated once
// IF(...) = the final calculation, reused sales_total twice without repeating the lookup

XLOOKUP runs a single time here, no matter how many times sales_total shows up in the IF logic. Compare that to the equivalent formula without LET, where the same XLOOKUP would need to be typed inside both branches of the IF, doubling the lookup cost and the room for a typo.

How LET Works

Naming rules are stricter than they look

Names must start with a letter and can't contain spaces or punctuation, though underscores are allowed. Excel reads names like ct1 or r1 as cell references rather than as your variable, so those names fail. count1 works fine. ct1 doesn't. A bare single letter r or c (or R/C) fails too, even with no trailing number at all, since Excel reserves both letters for R1C1-style references regardless of what follows them. Names also aren't case-sensitive, so Total and total refer to the same variable inside the LET.

Scope is local to the LET, not the workbook

Each name only exists inside the LET function that defines it, plus any functions nested inside it. Once the formula finishes evaluating, the name is gone. This matters because it means you can reuse the same name (x, result, total) across multiple unrelated LET formulas on the same sheet without any conflict.

The result must be the last argument, always

LET evaluates name/value pairs in order, then returns whatever the final argument produces. That final argument can be a raw calculation, or it can just be a previously defined name if you want to return one of your variables directly:

=LET(monthly_rate, C2/12, monthly_rate)

That formula defines monthly_rate and then returns it as-is. Pointless on its own, but it illustrates the rule: the last thing in LET is always what gets returned, whether it's a fresh calculation or a name you already built.

Each expression calculates once

This is the performance case for LET. In a normal formula, if VLOOKUP(A2, table, 3, FALSE) appears four times, Excel runs that lookup four times. Wrap it in a name inside LET, and Excel runs it once, then substitutes the cached result everywhere the name appears. On a small sheet you won't feel it. On a workbook with thousands of rows and array-heavy formulas, it's the difference between a snappy recalculation and a spinner.

LET pairs naturally with LAMBDA

LAMBDA lets you define a reusable custom function, and LET is frequently used inside it to name intermediate steps so the LAMBDA body stays readable. A LAMBDA with three nested calculations and no LET is nearly unreadable. The same LAMBDA with LET-named steps reads almost like plain English.

Common Use Cases

Eliminating duplicate lookups

You need a rep's name and their points total, and you'll reference both values twice further down the formula.

=LET(
    rep_name, XLOOKUP(A2, reps[ID], reps[Name]),
    rep_points, XLOOKUP(A2, reps[ID], reps[Points]),
    rep_name & " earned " & rep_points & " points, rank: " & RANK(rep_points, reps[Points])
)

// rep_name and rep_points each run once, then get reused in the final string and in RANK

Building a message or label formula

Sales dashboards often need a single readable sentence built from several pieces of data. Naming each piece keeps the final concatenation simple to audit.

=LET(
    q_sales, SUM(FILTER(sales[Amount], sales[Quarter]=B2)),
    q_target, XLOOKUP(B2, targets[Quarter], targets[Goal]),
    pct, q_sales / q_target,
    "Q" & B2 & ": " & TEXT(pct, "0%") & " of target"
)

Multi-stage calculations without helper columns

A loan payment calculator needs the monthly rate and number of periods before it can call PMT. Instead of burying those conversions inside the PMT arguments, name them first.

=LET(
    monthly_rate, C2/12,
    total_periods, D2*12,
    PMT(monthly_rate, total_periods, -E2)
)

Wrapping LET inside LAMBDA for a reusable function

Once a LET-based calculation gets used across multiple sheets, promote it to a named LAMBDA function so it can be called like a native formula.

=LAMBDA(price, qty, discount,
    LET(
        subtotal, price * qty,
        subtotal - (subtotal * discount)
    )
)
// Saved in Name Manager as NETPRICE, then called as =NETPRICE(A2, B2, C2)

Handling Errors

LET formulas most commonly throw #NAME? or #CALC!, and both trace back to how you wrote the formula rather than to the data it's working on.

Common causes of #NAME?:

• A variable name that Excel reads as a cell reference, like r1 or ct2
• A bare r or c used as a name, since both are reserved regardless of context
• A typo in a name between where you define it and where you use it later in the formula
• Punctuation or a space inside a name (only underscores are allowed)
• Calling LAMBDA or another 365-only function inside LET on a version that doesn't support it

Common causes of #CALC!:

• A name that references itself, creating a circular definition
• A name you use before you define it, since LET evaluates pairs strictly in order
• The final calculation argument missing entirely

=IFERROR(LET(rate, C2/12, PMT(rate, D2*12, -E2)), "Check inputs")

Notes & Gotchas

Why does LET return #NAME??

The most common cause is a variable name that Excel mistakes for a cell reference, such as ct1 or r5. Excel checks names against its cell reference pattern first, and if your name matches that pattern, it fails before your calculation ever runs. Rename the variable to something that can't be parsed as a reference, like count1 instead of ct1, and the error clears immediately.

What happens if a LET name conflicts with a name in Name Manager?

The local LET name wins inside that formula. Suppose you've already created a workbook-level defined name called total in Name Manager, and you then define a local total inside a LET function: every reference to total within that LET uses the local version. Excel ignores the Name Manager definition for the duration of that formula, which can cause confusing results if you forget the local name exists and expect the global one.

How deep can I nest LET functions?

There's no hard nesting limit from LET itself, but each nested LET creates its own scope, and names from an outer LET are visible inside a nested one, not the other way around. In practice, three or four levels of nesting is where formulas become hard to audit even with clear names. At that point, splitting the logic into a LAMBDA-based helper function is usually the better move.

Does LET work differently in Excel 2021 than in Excel 365?

LET itself, no. Once it shipped to Excel 2021, the function behaves identically to the 365 version, including the 126-pair limit and scoping rules. But a formula that combines LET with a 365-only function won't behave the same across both: LAMBDA, for instance, never made it into Excel 2021's fixed feature set, even though it arrived around the same time as LET did. The exact same LET-wrapped-in-LAMBDA formula runs fine in Excel 365 and throws #NAME? in Excel 2021, even though LET on its own works identically in both. Excel 2019 and earlier don't have LET at all, and there's no formula-based workaround. The closest substitute there is defining named ranges in Name Manager, but those are workbook-scoped and don't calculate an expression once the way LET does.

Can I return an array from LET?

Yes. The final calculation argument can be any valid Excel expression, including one that returns an array, so LET works fine feeding into FILTER, SORT, or other dynamic array functions. The named variables inside the LET can themselves hold arrays too, not just single values.

Related Functions

Function	Use this when...
XLOOKUP	Your LET formula needs a lookup, and you're avoiding VLOOKUP's column-number fragility.
IF	Your calculation branches on a condition and you want to name the tested value once inside LET.
IFERROR	You need to catch #NAME? or #CALC! from a malformed LET formula and show a fallback value instead.