How to Use LET in Google Sheets: Comprehensive Guide

LET lets you give names to values or calculations inside a formula and then reuse those names. It makes long formulas readable, because tax is clearer than a repeated B2*0.2, and faster, because each named calculation runs once no matter how many times you use it.

Syntax

=LET(name1, value1, [name2, value2, ...], formula_expression)

name1: A name you choose, such as price or data.
value1: The value or expression the name stands for.
name2, value2, ... [Optional]: More name/value pairs. Later values can use earlier names.
formula_expression: The final calculation, which uses the names. This is what LET returns.

The arguments always come as pairs, followed by one final expression, so the total number of arguments is odd.

Basic example

Without LET, a repeated calculation clutters the formula:

=IF(B2 * C2 > 1000, B2 * C2 * 0.9, B2 * C2)

With LET, it is named once:

=LET(total, B2 * C2, IF(total > 1000, total * 0.9, total))

total is calculated once and used three times. Anyone reading the formula sees the intent immediately.

Several names

Each name can build on the ones before it:

=LET(
  price, B2,
  qty, C2,
  subtotal, price * qty,
  tax, subtotal * 0.2,
  subtotal + tax
)

You can lay a formula out over several lines (press Ctrl+Enter or Cmd+Enter to add a line break in the formula bar), which is much easier to review.

Why it also speeds things up

When an expression such as FILTER(...) or XLOOKUP(...) appears several times in a formula, Sheets calculates it every time. With LET, you calculate it once:

=LET(
  match, XLOOKUP(E2, A2:A1000, B2:B1000),
  IF(match = "", "Not found", match)
)

Without LET, you would need to write the XLOOKUP twice. On big sheets, this can cut calculation time noticeably.

Practical patterns

Clean the data once, then use it

=LET(
  clean, TRIM(LOWER(A2)),
  IF(clean = "", "", PROPER(clean))
)

Reuse a filtered range

=LET(
  rows, FILTER(A2:C100, B2:B100 = "Sales"),
  IF(ROWS(rows) = 0, "None", SORT(rows, 3, FALSE))
)

rows holds the filtered table; it is checked and then sorted without running FILTER twice.

Percent of total

=LET(
  part, SUMIF(A2:A100, E2, C2:C100),
  total, SUM(C2:C100),
  part / total
)

Safe division

=LET(
  den, SUM(B2:B10),
  IF(den = 0, 0, SUM(A2:A10) / den)
)

Combine with LAMBDA, MAP and others

LET works inside functions like MAP and LAMBDA, so the readability benefits carry into array formulas.

=MAP(A2:A10, LAMBDA(x, LET(y, TRIM(x), IF(y = "", "", UPPER(y)))))

Naming rules

Names must start with a letter and may contain letters, numbers and underscores.
A name can't look like a cell reference: A1, B2 and C10 are not allowed, but a_1 or price are fine.
Avoid names of existing functions (sum, date), as they cause confusion and can cause errors.
Names are local to the LET formula. They don't exist outside of it.

Common errors

#NAME?. A name is spelled differently in two places, or it is used before it is defined. Names can only use earlier names.
"Wrong number of arguments to LET". The arguments must be pairs followed by exactly one final expression. A missing value or a missing final calculation is the usual cause.
Invalid name. It looks like a cell reference or starts with a number or symbol.
The result is just the last name. LET returns the final expression, not the named values. Make sure the last argument is the calculation you want.
Trying to use a name in another cell. Names exist only inside their own LET. For reusable logic, create a Named function with LAMBDA.

LET vs. helper columns vs. named ranges vs. LAMBDA

Helper columns show intermediate values in the sheet, which is great for auditing but adds columns.
Named ranges give a name to a range of cells, shared across the whole workbook.
LET names intermediate values inside a single formula.
LAMBDA creates a reusable function, optionally saved as a Named function.

Frequently asked questions

Does LET make formulas faster? It can: each named expression is computed once, even if it is used many times.

How many names can I use? As many as you like.

Can a name hold a whole range or array? Yes, a name can hold a range, an array or the result of FILTER.

Can I use LET inside ARRAYFORMULA? Yes. They work together, but many array functions already handle ranges natively.

Related Functions

LAMBDA: Create custom, reusable functions.
MAP: Apply a function to each item of an array.
FILTER: Return the rows that meet a condition.
IF: Return different values based on a condition.
ARRAYFORMULA: Apply a formula to a whole range.

LET