LET function
LET assigns names to intermediate values inside one formula so each is calculated only once, which makes long formulas shorter, faster and easier to read. Names exist only within that formula.
Example
Read it as a sentence: name the numbers in D2:D7, the Qty column, as x, then add them up, which gives 68.
Excel shows 68
D
1
Qty
2
12
3
7
4
20
5
5
6
15
7
9
Items
Syntax
LET(name1, name_value1, [name2, name_value2], ..., calculation)
Arguments in [square brackets] are optional.
-
name1(required) - A name for the first value; must start with a letter.
-
name_value1(required) - The value or formula assigned to that name.
-
name2, name_value2(optional) - More name and value pairs.
-
calculation(required) - The final expression using the names.
Returns: The result of the final calculation.
What Excel actually returns
Each formula below was run in desktop Excel. The result is exactly what the cell showed, errors included.
| Formula | Excel shows | Case |
|---|---|---|
=LET(x,2,y,3,x*y) | 6 | Two names |
=LET(x,2,y,x+1,y) | 3 | Later name uses earlier |
=LET(x,{1,2,3},x*10) | 10 | Array spills |
=LET(f,LAMBDA(n,n+1),f(1)) | 2 | Lambda name |
=LET(A1,5,A1) | 5 | Cell-like name |
=LET(x,Items!D2:D7,SUM(x)) | 68 | Range name |
The Items sheet these formulas read
D
1
Qty
2
12
3
7
4
20
5
5
6
15
7
9
Items
Watch out for
- The last argument must be the calculation, not another pair.
- Names cannot look like cell references (e.g. A1 or R2C3).
- Names exist only within that formula.
Where it works
- Excel
- LET arrived in Excel 2021. Older versions do not have it.
- Google Sheets
- Same name, same behaviour for everyday use.
Part of Logical functions. Every result on this page was measured in desktop Excel, build 16.0.20527. Last updated .