LET function

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 next value_expressions and the formula_expression. This must be an identifier (details below), and usage is case-insensitive.
• 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).
• name2…: [ OPTIONAL ] Repeatable, additional names to be assigned.
• value_expression2…: [ OPTIONAL ] Repeatable, additional value_expressions to be evaluated.
• formula_expression: Formula to be calculated. It uses names declared in the LET 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:

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:

Make a Copy

Filter data & replace blank cell with dash using LET

Return all data for “Fred” and replace blank cells with dash.

Example Data:

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:

Make a Copy

Common Errors

Related functions

• LAMBDA