LET function

LET lets you name a single intermediate result inside a formula, then return that result, making complex calculations clearer and easier to read.

=LET(name1, value1, calculation)

Generate a LET formula

Describe what you need. The generator will reach for LET where LET is the right tool, and tell you when it is not.

How to get a better answer
  • Name your columns by letter and by header: "column F (Net Value)" beats "the amount column".
  • State every condition, including the negatives — "not cancelled" changes the formula's shape.
  • Say where the data starts if it is not row 1, and whether it will grow.
  • Check the settings above match your spreadsheet: the wrong argument separator is a syntax error on your machine.

Arguments

How LET reads its arguments
name1requiredvalue1requiredcalculationrequiredLET →
ArgumentRequiredDescription
name1RequiredA text string that becomes the variable name; it must follow normal naming rules (no spaces, cannot start with a number).
value1RequiredThe value assigned to name1; can be a constant, cell reference, array, or any expression that resolves to a value.
calculationRequiredThe final expression that can use name1; its result is what LET returns. If it references an undefined name, #NAME? is raised.

Returns

LET returns a single value of whatever type the final calculation produces – a number, text, date, or array.

Availability

Excel: 365 · Google Sheets: Not available

Worked examples

1. Total quantity of all stock items

IngredientSupplierUnitQtyExpiry
TomatoesFreshFarmkg202024-05-01
Olive OilGourmetCoL152025-01-15
CheeseDairyBestkg82024-03-20
BasilHerbGardeng2002023-12-05
FlourBakerySupplykg302025-06-30
=LET(totalQty, SUM(D2:D6), totalQty)

Result: 73

The LET function first computes the sum of the Qty column (D2:D6), which adds 20 + 15 + 8 + 200 + 30 = 73. That sum is stored in the variable totalQty, and the final argument simply returns totalQty, so the cell shows 73.

2. Earliest expiry date in stock

IngredientSupplierUnitQtyExpiry
TomatoesFreshFarmkg202024-05-01
Olive OilGourmetCoL152025-01-15
CheeseDairyBestkg82024-03-20
BasilHerbGardeng2002023-12-05
FlourBakerySupplykg302025-06-30
=LET(earliest, MIN(E2:E6), earliest)

Result: 2023-12-05

MIN applied to the Expiry column (E2:E6) returns the earliest serial date, which corresponds to 5 December 2023. LET stores that date in the variable earliest and then returns it, so the formula displays the date.

3. Average quantity supplied by FreshFarm

IngredientSupplierUnitQtyExpiry
TomatoesFreshFarmkg202024-05-01
Olive OilGourmetCoL152025-01-15
CheeseDairyBestkg82024-03-20
BasilHerbGardeng2002023-12-05
FlourBakerySupplykg302025-06-30
=LET(avgQty, AVERAGEIFS(D2:D6, B2:B6, "FreshFarm"), avgQty)

Result: 20

AVERAGEIFS looks at the Qty column (D2:D6) but only includes rows where the Supplier column (B2:B6) equals "FreshFarm". In this tiny data set only the first row matches, so the average is simply 20. LET captures that in avgQty and returns it.

Common errors

Which LET error are you seeing?
LET returned an error#NAME?
Define the missing variable as the first argument pair, e.g., =LET(x, 5, x) instead of referencing y.
#VALUE!
Ensure value1 is numeric when the calculation expects a number, or wrap it in VALUE() to coerce text to a number.
#REF!
Adjust the range reference to a valid address or restore the missing cells.
ErrorWhy it happensHow to fix it
#NAME?The calculation argument references a variable that was never defined in the LET call.Define the missing variable as the first argument pair, e.g., =LET(x, 5, x) instead of referencing y.
#VALUE!The value1 argument returns a type that cannot be used in the calculation, such as trying to sum text strings.Ensure value1 is numeric when the calculation expects a number, or wrap it in VALUE() to coerce text to a number.
#REF!A range used inside LET points to a cell that has been deleted or is outside the worksheet, causing an invalid reference.Adjust the range reference to a valid address or restore the missing cells.

Tips and when to use something else

  • Use LET when you need to reuse a single intermediate result many times; for multiple intermediates switch to LAMBDA, which supports several name/value pairs.
  • Because LET only accepts one name/value pair, nest multiple LET calls if you really need more than one variable.
  • If you find yourself only naming a range for readability, consider using a named range (Formulas → Name Manager) instead of LET.
  • When the goal is to look up a single value based on another column, XLOOKUP or VLOOKUP is often clearer than a LET-wrapped SUMIF.

Related functions

IFThe IF function returns one value if a condition is true and another (or nothing) if it is false, letting you branch logic in a cell.XLOOKUPXLOOKUP returns the value from a specified return array that corresponds to a given lookup value, with optional handling for missing matches and search direction.LAMBDALAMBDA lets you create custom, reusable functions directly in a cell, returning any value or array based on supplied parameters.BYROWBYROW applies a LAMBDA to each row of a range and returns a vertical array of the results, enabling row-wise calculations in Excel 365 and Google Sheets.MAPMAP applies a custom LAMBDA to each element of a range, returning a new array of the same shape with the calculated results.

Frequently asked questions

Can LET be used to create an array that spills across multiple cells?
Yes. If the calculation argument returns an array, LET will pass that array back and Excel will attempt to spill it. For example, =LET(arr, SEQUENCE(3), arr) produces a 3-row vertical spill. The same rule that governs normal spill behavior applies.
Why does my LET formula return #NAME? even though I spelled the variable correctly?
The most common cause is a missing closing parenthesis or an extra comma that makes Excel think the variable name is part of the calculation instead of the first argument. Double-check the syntax: LET(name, value, calculation). Also ensure the variable name does not conflict with a built-in function name.
Is it possible to nest LET inside another LET?
Absolutely. Nesting lets you create multiple independent variables without the need for LAMBDA. For example, =LET(a, 5, LET(b, a*2, b+1)) returns 11. Each inner LET has its own scope, but can still reference variables from the outer LET.
Does LET work in Google Sheets?
No. Google Sheets does not currently support the LET function, so you must achieve similar results with named ranges, helper columns, or the LAMBDA-like approach using ARRAYFORMULA combined with other functions.

Need a different formula?

The full generator is not scoped to one function — describe any spreadsheet problem and it will pick.

Open the formula generator

Reviewed 2026-09-17