Name a constant
| A | |
|---|---|
| 1 | Net |
| 2 | 200 |
Formula: C1: =LET(tax,0.0825,A2*(1+tax))
Result: 216.5
tax is defined once, then used in the calculation.
Dynamic arrays
LET
Names for intermediate results inside a formula.
Syntax
LET(name1, value1, …, calculation)Example: =LET(x,A2*2,x+1)
Available in: Excel 2021+, Microsoft 365
LET gives names to values or calculations inside a formula, so each is computed once and reused: =LET(name1, value1, …, calculation). It makes long formulas readable and can speed up heavy ones.
When to use it
- The same expression appears several times in one formula.
- A formula is hard to read and you want meaningful names.
- You wrap a heavy lookup in IFERROR and want to avoid calculating it twice.
- You build formulas with several intermediate steps.
Arguments explained
- name1. A name you invent for a value, such as tax or total. Names must start with a letter and cannot look like cell references.
- name_value1. The value or calculation assigned to name1. It is evaluated once and can be reused any number of times.
- name2, name_value2, …. Further names, in pairs. Later values may use earlier names.
- calculation. The last argument is the formula that produces the final result and uses the names you defined.
Worked examples
Reuse a sum
| A | |
|---|---|
| 1 | Share |
| 2 | 25 |
| 3 | 50 |
| 4 | 25 |
Formula: C1: =LET(total,SUM(A2:A4),A2/total)
Result: 0.25
total is calculated once and reused; here 25 / 100.
Intermediate result in a test
| A | B | |
|---|---|---|
| 1 | Spend | Budget |
| 2 | 120 | 100 |
Formula: D2: =LET(ratio,A2/B2,IF(ratio>1,"Over","Within"))
Result: Over
ratio is named once and used in the IF.
Try it
A tiny spreadsheet with example data. Edit the formula or any number and see the result.
Formula in F2: =LET(total,SUM(C2:C7),total/COUNT(C2:C7))
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Region | Product | Units | Price | Result | |
| 2 | East | Pen | 120 | 2.5 | 58.3333333333 | |
| 3 | West | Pen | 80 | 2.5 | ||
| 4 | East | Book | 45 | 12 | ||
| 5 | North | Book | 60 | 12 | ||
| 6 | West | Lamp | 30 | 25 | ||
| 7 | East | Lamp | 15 | 25 |
Result in F2: 58.3333333333
Common errors and how to fix them
- #NAME?. LET is available in Excel 2021 and Microsoft 365. Older versions do not know it.
- Invalid name. Names cannot look like cell references (such as x1) or contain spaces.
- Last argument missing. The final argument must be the calculation that returns the result, not another name pair.
- Name used before it is defined. Names are available only after they are defined, and not outside the formula.
Related functions
Frequently asked questions
- Does LET make formulas faster?
- It can: a repeated expensive calculation runs once instead of several times.
- Can later names use earlier names?
- Yes. Each name can use the ones defined before it.
- What is the difference between LET and a named range?
- A named range is workbook-wide. LET names exist only within the formula they are in.
- How many names can LET have?
- Up to 126 name and value pairs.