Practice free

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.

Practice this in the app

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.

FormulaExcel showsCase
=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

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 .