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

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

LET and LAMBDA make long, repeated Excel formulas readable and reusable. LET gives intermediate values readable names inside one formula, so each is calculated once, and LAMBDA turns any formula into a named function you can call like a built-in one. LET needs Excel 365, 2021 or 2024; LAMBDA needs Excel 365 or 2024.

The Complete Excel Course with AI Integration

From formulas and pivot tables to Power Query, DAX, VBA and AI with Copilot, ChatGPT and Claude — hands-on from the first lesson.

By Sagnik Bhattacharya and Virginia Thorn, Primetime Emmy Award winner

Buy now

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.

Which Excel version do you need for LET and LAMBDA?

LET needs Excel 365, Excel 2021 or Excel 2024, while LAMBDA needs Excel 365 or Excel 2024.

  • Excel 365 or Excel 2024 — Excel 2021 has LET but not LAMBDA, and Excel 2019, 2016 and older versions have neither. A version without the function returns #NAME? because Excel does not know the function name. 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.

What does the LET function do in Excel?

The LET function names intermediate values inside one formula, so Excel calculates each repeated expression once and the formula reads in plain terms.

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.

What does the LAMBDA function do in Excel?

The LAMBDA function lets you build your own Excel function from a formula, with named parameters, so one definition replaces the same formula copied into many cells.

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 on its own in a cell (Excel returns a #CALC! error). You define it in the Name Manager and then call it by name.

How to create a named LAMBDA

  1. Go to Formulas tab → Name Manager → New.
  2. Give it a name (e.g. CalcMargin). Names cannot contain spaces.
  3. In the "Refers to" box enter the LAMBDA definition: =LAMBDA(revenue, cost, (revenue-cost)/revenue).
  4. Click OK and close Name Manager.
  5. 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 Complete Excel Course with AI Integration

From formulas and pivot tables to Power Query, DAX, VBA and AI with Copilot, ChatGPT and Claude — hands-on from the first lesson.

By Sagnik Bhattacharya and Virginia Thorn, Primetime Emmy Award winner

Buy now

Can you use LET inside a LAMBDA?

Yes, you can use LET inside a LAMBDA: the LAMBDA body accepts any formula, including a full LET block.

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.

If you want to build formulas like these with an instructor, try The Complete Excel Course with AI Integration on Coding Liquids.

When should you use LET, LAMBDA or neither?

Use LET for one complex formula that repeats a sub-expression, LAMBDA when the same logic appears in three or more cells, and neither for simple logic.

SituationBest approach
One complex formula that repeats a sub-expressionLET inside that formula
The same logic appears in 3+ cells or on multiple sheetsLAMBDA in Name Manager
The LAMBDA body itself is complexLET inside the LAMBDA body
Logic is simple (single IF or SUMIF)Neither — use the formula directly
Logic needs to loop through an arrayMAP 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 returns a #CALC! error. Test an unnamed LAMBDA by passing it 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 2021 or older, cells using your LAMBDA show #NAME?. Tell them the version requirement upfront.

Troubleshooting

  1. #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.
  2. #CALC! when you enter a LAMBDA in a cell
    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).
  3. 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.
  4. 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.
  5. #CALC! error inside a LAMBDA that uses FILTER or UNIQUE
    FILTER returns #CALC! when no rows match, because Excel does not support empty arrays: give FILTER its third argument, if_empty, such as "None". A blocked spill area shows #SPILL! instead, so for that error check for merged cells or non-blank values in the output range.

The Complete Excel Course with AI Integration

From formulas and pivot tables to Power Query, DAX, VBA and AI with Copilot, ChatGPT and Claude — hands-on from the first lesson.

By Sagnik Bhattacharya and Virginia Thorn, Primetime Emmy Award winner

Buy now

FAQ

What Excel version do I need for LET and LAMBDA?
Excel 365 (a Microsoft 365 subscription) or Excel 2024 has both. Excel 2021 has LET but not LAMBDA, and Excel 2019 and 2016 have neither, so a version that lacks a function returns #NAME? because Excel does not recognise the name. On an older version, VBA is the closest alternative for reusable logic. Google Sheets also has LET and LAMBDA; there, reusable custom functions live under Data → Named functions instead of a Name Manager.
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.

Related tutorials

Sources