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)| Part | What it is |
|---|---|
name1 | A name for the first value. It must start with a letter and cannot look like a cell reference. |
name_value1 | The value or formula the name stands for. |
name2, name_value2 | Optional further pairs. A later pair can use an earlier name. |
calculation | The 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.