This function assigns a name with the value_expression
results and returns the result of the formula_expression
.
Sample Usage
LET(avg, AVERAGE(B2:D2), IF(avg>=4, "Great", IF(avg>=3, "Good", "Poor")))
: Categorize an average value.
LET(criteria, "Fred", range, FILTER(A2:D8, A2:A8=criteria), ARRAYFORMULA(IF(ISBLANK(range), "", range)))
: Filter data and replace blank cell with dash.
Syntax
LET(name1, value_expression1, [name2, …], [value_expression2, …], formula_expression )
name1
: A name used inside the nextvalue_expressions
and theformula_expression
. This must be an identifier (details below), and usage is caseinsensitive.value_expression1
: Formula whose result can be referred to later with the name that was declared before. It can use names declared in the previous parameters. For example,
AVERAGE(B2:D2)
.
 For example,
name2…
: [ OPTIONAL ] Repeatable, additionalnames
to be assigned.value_expression2…
: [ OPTIONAL ] Repeatable, additionalvalue_expressions
to be evaluated.formula_expression
: Formula to be calculated. It usesnames
declared in theLET
function.
Tip: You can use formula_expression
as the names
defined in the scope of the LET
function. The value_expressions
evaluates only once in the LET
function, even if the next value_expressions
or the formula_expression
uses them multiple times.
Examples
Categorize the average value of product ratings with LET
Example Data:
A 
B 
C 
D 


1 
Product 
January ratings 
February ratings 
March ratings 
2 
Red tshirt 
3.5 
4 
3 
3 
Black jeans 
4.5 
5 
3.5 
4 
Hat 
3 
2.5 
2 
With LET: Input this formula in E2
and drag the blue box around the cell down to fill E3
and E4
.
=LET(avg, AVERAGE(B2:D2), IF(avg>=4, "Great", IF(avg>=3, "Good", "Poor")))
Without LET: Input this formula in E2
and drag the blue box around the cell down to fill E3
and E4
.
=IF(AVERAGE(B2:D2)>=4, "Great", IF(AVERAGE(B2:D2)>=3, "Good", "Poor"))
Result:
A 
B 
C 
D 
E 


1 
Product 
January ratings 
February ratings 
March ratings 
Average Rating Category 
2 
Red tshirt 
3.5 
4 
3 
Good 
3 
Black jeans 
4.5 
5 
3.5 
Great 
4 
Hat 
3 
2.5 
2 
Poor 
Filter data & replace blank cell with dash using LET
Return all data for “Fred” and replace blank cells with dash.
Example Data:
A 
B 
C 
D 


1 
Rep 
Region 
Product 
Profit 
2 
Amy 
East 
Apple 
$1.33 
3 
Fred 
South 
Banana 
$0.09 
4 
Amy 
West 
Mango 
$1.85 
5 
Fred 
North 
$0.82 

6 
Fred 
West 
Banana 
$1.25 
7 
Amy 
East 
Apple 
$0.72 
8 
Fred 
North 
Mango 
$0.54 
With LET: Input this formula in E2
:
=LET(criteria, "Fred", range, FILTER(A2:D8, A2:A8=criteria), ARRAYFORMULA(IF(ISBLANK(range), "", range)))
Without LET: Input this formula in E2
:
=ARRAYFORMULA(IF(ISBLANK(FILTER(A2:D8, A2:A8="Fred")), "", FILTER(A2:D8, A2:A8="Fred")))
Result:
E 
F 
G 
H 


1 
Rep 
Region 
Product 
Profit 
2 
Fred 
South 
Banana 
0.09 
3 
Fred 
North 
 
0.82 
4 
Fred 
West 
Banana 
1.25 
5 
Fred 
North 
Mango 
0.54 
Common Errors
The name argument isn’t an identifierExample: =LET(B2, AVERAGE(B2:D2), IF(B2>=4, "Great", IF(B2>=3, "Good", "Poor")))
If the argument isn’t an identifier, this error occurs:
Identifier requirements:
 Can’t be ranges, like “A1” or “A2”
 Can’t have spaces or special characters
 Can’t start with numbers, like “9hello”
In a value_expression
inside the LET
function, this syntax lets you use the named arguments which have already been declared earlier. For example, “left in the function.”
Example:
LET(x, 1, y, LET(z, 2, x+z), x+y) 
CORRECT — x has been declared before using it in the inner LET function. 

LET(y, LET(z, 2, x+y), x, 1, x+y) 
WRONG — using x before its declaration. 
If you use an argument before it’s declared, this error occurs: