What Excel version do I need for LET and LAMBDA?

Both LET and LAMBDA require Excel 365 (Microsoft 365 subscription) or Excel 2021. They are not available in Excel 2019, Excel 2016, or Google Sheets. If you try to use them in an older version you will get a #NAME? error because Excel does not recognise the function name.

What is the difference between LET and LAMBDA?

LET is used inside a single formula to name intermediate values, making that formula easier to read and avoiding repeated calculations. LAMBDA turns a formula into a reusable named function that can be called anywhere in the workbook — it is stored in the Name Manager and works like a custom worksheet function. Think of LET as a local variable and LAMBDA as a function definition.

Can I use LET inside a LAMBDA?

Yes, and this is the most powerful pattern. Wrapping LET inside your LAMBDA body lets you break complex logic into named steps while keeping the entire thing reusable as a single named function. Define the LAMBDA in the Name Manager, then inside its body use LET to name intermediate calculations before the final result expression.

Can I share a LAMBDA function with a colleague?

LAMBDA functions live in the workbook's Name Manager, so they travel with the .xlsx or .xlsm file. To share a LAMBDA, send the file. If you want to use the same LAMBDA in a different workbook, you need to redefine it in that workbook's Name Manager — there is no global Excel function library for user-defined LAMBDAs.

When should I use LAMBDA instead of a named range?

Use a named range when you want to give a fixed cell reference a readable name (e.g. TaxRate = $B$2). Use LAMBDA when you want a reusable calculation that accepts input arguments — for example, a LAMBDA called CalcTax that takes revenue and a rate as parameters and returns the tax amount. Named ranges are constants; LAMBDAs are functions.

Excel

LET and LAMBDA in Excel: Turn Repeated Formulas Into Reusable Logic

By Sagnik Bhattacharya 1 Feb 2026 4 min read

Coding Liquids blog cover featuring Sagnik Bhattacharya for LET and LAMBDA in Excel, with reusable formula logic visuals.

LET and LAMBDA are two of the most useful additions to Excel's formula language in the past decade — and most Excel users ignore them because the documentation makes them sound harder than they are. LET gives intermediate values inside a formula readable names instead of raw expressions. LAMBDA turns any formula into a reusable named function you can call like a built-in Excel function.

Coming Soon

Complete Excel Guide with AI Integration

Master formulas, pivot tables, data analysis, and charts — with AI integration.

Learn more

Together they solve a problem every analyst eventually hits: a workbook full of 12-level nested formulas that nobody — including you six months later — can read, and the same business rule copy-pasted into 40 separate cells so that fixing one mistake requires finding all 40.

This tutorial covers LET and LAMBDA separately with concrete examples, then shows how to combine them. By the end you will have three working formulas you can adapt to your own workbook.

Prerequisites

Excel 365 or Excel 2021 — LET and LAMBDA are not available in Excel 2019, 2016, or any older version. In those versions the formula bar returns #NAME? because Excel does not know these function names. If you are on an older version, the closest alternative is VBA (see how to write VBA with Claude AI).
A grasp of basic Excel formulas — you should be comfortable with IF, VLOOKUP, or SUMIFS before learning LET/LAMBDA. The advanced formulas guide covers the foundation.

LET: Name Intermediate Values Inside a Formula

What LET does

LET lets you assign a name to an expression inside a formula, then reuse that name later in the same formula. The syntax is:

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

The last argument is always the result expression. Everything before it is a name-value pair.

LET Example 1: Readable profit margin

Without LET:

=IF((C2-B2)/C2>0.2, "High margin", IF((C2-B2)/C2>0.1, "Medium", "Low"))

With LET — the margin calculation happens once and is readable:

=LET(
    revenue, C2,
    cost, B2,
    margin, (revenue - cost) / revenue,
    IF(margin > 0.2, "High margin", IF(margin > 0.1, "Medium", "Low"))
)

The formula is longer in lines but clearer to read, and (revenue - cost) / revenue is calculated once — not three times as in the original.

Beginner pitfall: The last argument must be the result, not a name-value pair. If you accidentally write =LET(x, 5, y, x*2, y, "done") Excel will return "done" not 10 — the last argument wins.

LET Example 2: Simplifying a nested XLOOKUP

A report pulls the product name and price from two different lookup tables. Without LET, the lookup key expression is repeated in both lookups:

=XLOOKUP(TRIM(UPPER(A2)), Products[SKU], Products[Name]) &
" — " &
TEXT(XLOOKUP(TRIM(UPPER(A2)), Products[SKU], Products[Price]), "£#,##0.00")

With LET:

=LET(
    sku, TRIM(UPPER(A2)),
    name, XLOOKUP(sku, Products[SKU], Products[Name]),
    price, XLOOKUP(sku, Products[SKU], Products[Price]),
    name & " — " & TEXT(price, "£#,##0.00")
)

TRIM(UPPER(A2)) is evaluated once and reused — faster and easier to correct if the lookup key normalisation ever changes.

LAMBDA: Turn a Formula Into a Reusable Function

What LAMBDA does

LAMBDA creates a custom function. You define it once in the Name Manager, give it a name, and then call it like any built-in Excel function from any cell in the workbook. The syntax:

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

You never enter a LAMBDA directly into a cell (Excel will prompt you for a value). You define it in the Name Manager and then call it by name.

How to create a named LAMBDA

Go to Formulas tab → Name Manager → New.
Give it a name (e.g. CalcMargin). Names cannot contain spaces.
In the "Refers to" box enter the LAMBDA definition: =LAMBDA(revenue, cost, (revenue-cost)/revenue).
Click OK and close Name Manager.
In any cell type =CalcMargin(C2, B2) — Excel calls your function.

LAMBDA Example 1: Business day countdown

A project tracker needs to count working days between today and a deadline, excluding weekends. The formula =NETWORKDAYS(TODAY(), A2)-1 appears in 30 cells. If the definition of "working days" changes (e.g. to exclude bank holidays), you update 30 formulas.

With LAMBDA, define WorkingDaysLeft in Name Manager:

=LAMBDA(deadline, NETWORKDAYS(TODAY(), deadline) - 1)

Now every cell uses =WorkingDaysLeft(A2). When the rule changes, update the Name Manager definition once.

LAMBDA Example 2: Tiered commission rate

A sales workbook applies a tiered commission rate: 5% below £10k, 8% from £10k–£50k, 12% above £50k. Without LAMBDA this nested IFS lives in every row of the commission column. Define CommissionRate:

=LAMBDA(sales,
    IFS(
        sales >= 50000, 0.12,
        sales >= 10000, 0.08,
        TRUE, 0.05
    )
)

Use it as =CommissionRate(D2)*D2. The logic is in one place; auditing it means opening the Name Manager, not hunting through 500 rows.

The Combination: LET Inside a LAMBDA

This is the pattern that makes complex logic both reusable and readable. Define a LAMBDA whose body uses LET to name intermediate steps.

Example: a revenue quality check that classifies each row as "Clean", "Missing cost", or "Margin below threshold".

=LAMBDA(revenue, cost, threshold,
    LET(
        margin, IF(revenue = 0, 0, (revenue - cost) / revenue),
        hasCost, cost > 0,
        IF(NOT(hasCost), "Missing cost",
            IF(margin < threshold, "Margin below threshold",
                "Clean"))
    )
)

Name this QualityCheck in Name Manager. Usage: =QualityCheck(C2, B2, 0.1). The body is readable (named variables), the function is reusable (defined once), and changing the classification logic means one edit in Name Manager.

When to Use LET, LAMBDA, or Neither

Situation	Best approach
One complex formula that repeats a sub-expression	LET inside that formula
The same logic appears in 3+ cells or on multiple sheets	LAMBDA in Name Manager
The LAMBDA body itself is complex	LET inside the LAMBDA body
Logic is simple (single IF or SUMIF)	Neither — use the formula directly
Logic needs to loop through an array	MAP or SCAN + LAMBDA — see MAP, SCAN, REDUCE guide

Common Mistakes

Putting the result expression before the last position in LET — every argument pair before the final one is a name-value binding. The result must come last.
Entering a LAMBDA directly into a cell without arguments — Excel will prompt you for a value. Test an unnamed LAMBDA by passing itself a sample value: =LAMBDA(x, x*2)(5) returns 10.
Using LAMBDA before the underlying logic is stable — if the business rule changes every week, a copy-paste formula is easier to patch than a named function that 30 cells depend on.
Naming a LAMBDA with a space or a reserved function name — names must follow Excel named range rules: no spaces, no leading numbers, not the same as an existing function (e.g. don't name it SUM).
Sharing the workbook without realising the LAMBDA is lost — if a colleague opens the workbook in Excel 2019, cells using your LAMBDA show #NAME?. Tell them the version requirement upfront.

Troubleshooting

#NAME? when you type =CalcMargin()
The Name Manager entry has a typo, or you saved it with a leading space. Go to Formulas → Name Manager, find the LAMBDA, and check the name matches exactly what you type in the cell.
Excel prompts "Please enter a value for…" when you use a LAMBDA
You entered the LAMBDA definition directly into a cell instead of calling it by name. LAMBDAs must be named in Name Manager first. To test an unnamed LAMBDA inline, self-apply it: =LAMBDA(x, x*2)(5).
LET returns a wrong value for one of the names
Name-value pairs are evaluated left to right. If you reference a name before it is defined (earlier in the list), LET will error or return an unexpected result. Reorder so each name is defined before it is used.
The LAMBDA works in one cell but the wrong column is highlighted as the result
Check whether the result expression (last argument) is actually returning what you expect. Add a temporary cell with just the intermediate LET name to verify each step.
#CALC! error inside a LAMBDA that uses FILTER or UNIQUE
Dynamic array functions inside a LAMBDA can fail when the spill area is blocked. The LAMBDA result is still a spill — check for merged cells or non-blank values in the output range.

FAQ

What Excel version do I need for LET and LAMBDA?: Both require Excel 365 (Microsoft 365 subscription) or Excel 2021. They are not available in Excel 2019 or 2016 — you will get #NAME? if you try.
What is the difference between LET and LAMBDA?: LET is a local variable — it names intermediate values inside one formula. LAMBDA is a function definition — you store it in the Name Manager and call it by name from anywhere in the workbook. Think: LET is scoped to one cell, LAMBDA is scoped to the whole workbook.
Can I use LET inside a LAMBDA?: Yes — this is the most powerful pattern. The LAMBDA body can be as complex as you like, including full LET blocks. Define the LAMBDA in Name Manager with a LET inside its body to get a function that is both reusable and readable.
Can I share a LAMBDA with a colleague?: LAMBDAs travel with the workbook file. Send the .xlsx or .xlsm and the LAMBDA is available. If they need it in a different workbook, they must redefine it in that file's Name Manager. There is no global LAMBDA library in Excel.
When should I use LAMBDA instead of a named range?: Named ranges are constants (a fixed cell reference or value). LAMBDAs accept arguments and compute a result. Use a named range for a fixed input like a tax rate cell. Use LAMBDA for a reusable calculation that takes variable inputs like revenue and cost.