Formuladrill

Dynamic arrays Updated November 2026

LET in Excel

LET(name1, value1, [name2, value2], ..., calculation) assigns names to values inside a single formula and then returns the result of the last argument, which can use those names. A step that would be repeated is written and calculated once.

Syntax

LET(name1, name_value1, [name2, name_value2], ..., calculation)
PartWhat it is
name1A name for the first value. It must start with a letter and cannot look like a cell reference.
name_value1The value or formula the name stands for.
name2, name_value2Optional further pairs. A later pair can use an earlier name.
calculationThe last argument: the formula that uses the names and gives the result.

Examples

=LET(total, B2*C2, IF(total>1000, total*0.9, total))

Result: The line total, with 10% off when it is above 1,000

Without LET, B2*C2 is written three times.

=LET(rate, 0.05, sales, SUM(B2:B13), sales*rate)

Result: 5% of the yearly total

Two names, then the calculation.

=LET(x, XLOOKUP(E2, A:A, C:C, ""), IF(x="", "Missing", x*1.2))

Result: Missing, or the looked-up price plus 20%

The lookup runs once instead of twice.

=LET(net, B2-C2, tax, net*0.21, net-tax)

Result: Net income after tax

tax uses net, which was defined before it.

Common mistakes

  • #NAME? in Excel 2019 or older: LET needs Excel 2021 or Microsoft 365.
  • A name such as A1, R1 or TAX2 is read as a cell reference and is rejected. Use a plain word.
  • An even number of arguments: the last argument must be the calculation, on its own.
  • Using a name before it is defined. Names are read left to right.

Practice LET

These drills check your answer on live data, free, no account needed.

  • Name a step with LETNet sales are sales minus returns. When net sales reach the target in H1 the bonus is 5% of net sales, otherwise it is 0. Use LET to name the net sales once and reuse the name. Fill D2 and D3.

Questions

Does LET make a formula faster?

Yes, when the named step would be repeated. Excel calculates each name once and reuses the result.

Can I use the names in other cells?

No. Names created by LET exist only inside that formula. For a name the whole workbook can use, define it in Name Manager or write a LAMBDA.

How many names can LET have?

Up to 126 name and value pairs.

Related

  • IFHow the Excel IF function returns one value when a test is true and another when false, with pass or fail, blank cell, nested IF and IF with AND examples.
  • XLOOKUPHow XLOOKUP works in Excel: find a value in one column and return the match from another, in any direction, with a not found value and next-smaller match.
  • FILTERHow the Excel FILTER function returns all the rows of a range that meet a condition, with examples for one criterion, AND and OR, a not found value and SORT.
  • IFSHow the Excel IFS function checks several conditions in order and returns the first match, with grade and shipping tier examples and the fix for #N/A.

Previous: IFNANext: TEXTBEFORE and TEXTAFTER