Excel LET Function
last modified October 5, 2026
The LET function assigns names to intermediate calculations,
values, or ranges inside a formula. It works like a variable in a programming
language: you name a piece of a calculation once, then reuse it by name as many
times as needed. This improves readability and can improve performance by
computing a value only once. The names exist only inside the formula that
defines them. This tutorial provides a comprehensive guide to using the
LET function with detailed examples. You'll learn the syntax,
naming rules, how names build on each other, and how to combine
LET with ranges, dynamic arrays, and LAMBDA.
LET Function Basics
The LET function takes pairs of names and values, followed by a
calculation that uses them. The last argument is always the calculation, and it
is required. The syntax is structured and logical.
| Component | Description |
|---|---|
| Function Name | LET |
| Syntax | =LET(name1, name_value1, [name2, name_value2, ...], calculation) |
| Arguments | One or more name/value pairs followed by a calculation |
| Return Value | Result of the final calculation |
| Availability | Excel for Microsoft 365, Excel 2021, Excel 2024, and Excel for the web |
This table breaks down the essential components of the LET
function. It shows the function name, basic syntax format, argument structure,
return value characteristics, and the versions that support it.
| Argument | Meaning |
|---|---|
| name1, name2, ... | A name to assign. It must start with a letter or an underscore and must not conflict with a cell reference or range syntax. |
| name_value1, name_value2, ... | The value assigned to the preceding name. A value can be a number, text, a cell reference, an array, or a calculation. |
| calculation | The final expression that LET evaluates and returns. It
must be the last argument and it is required. |
At least one name/value pair is required, and LET supports up to
126 pairs. Because the calculation is a single extra argument, a valid
LET formula always has an odd number of arguments. If the
calculation is omitted, so that the argument count is even, Excel returns a
#VALUE! error. LET is available in Excel for Microsoft
365, Excel 2021, Excel 2024, and Excel for the web, but not in Excel 2019 or
earlier.
LET Naming Rules
The names you create follow the same rules as names in the Name Manager. The following table summarizes the most important restrictions.
| Rule | Detail |
|---|---|
| First character | A name must begin with a letter or an underscore, not a number. |
| Allowed characters | Use letters, numbers, and underscores. Spaces and punctuation, such as a period, are not allowed. |
| No cell references | A name cannot look like a cell reference. x1 and
ct1 are invalid, while count1 is valid. |
| No R1C1 references | Single-letter names such as c and r conflict
with R1C1 style references and are rejected. |
| Not a function name | Avoid names such as SUM or IF that duplicate
a built-in function, because the collision makes the formula ambiguous. |
| Case-insensitive | total and TOTAL refer to the same name. |
| Local scope | Names exist only inside the formula. Other cells cannot see them. |
Names are defined in order, and each name can use the names defined before it.
A name cannot refer to a name that appears later in the same formula. If a
LET name matches an existing named range, the local
LET name takes precedence inside the formula.
Basic LET Example
This example demonstrates the simplest use of the LET function to
assign a value to a name and use it in a calculation.
=LET(x, 10, x * 2)
This formula assigns the value 10 to the name x, then calculates
x * 2. The result is 20. This shows how LET stores an
intermediate value so it can be reused without repeating the expression.
LET with Multiple Variables
LET can define multiple variables in a single formula. This
example assigns two literal values and multiplies them.
=LET(x, 5, y, 10, x * y)
This formula assigns 5 to x and 10 to y, then
calculates x * y. The result is 50. Variables can also come from
cells, as the next table shows.
| A | B |
|---|---|
| 5 | |
| 10 | |
| =LET(a, A1, b, A2, a + b) |
The table shows a simple spreadsheet with values in column A and a
LET formula in cell B3 that assigns names to cell values and
performs a calculation.
=LET(a, A1, b, A2, a + b)
This formula assigns A1 to a (5), A2 to b (10), then
calculates a + b. The result is 15. Multiple variables make
complex formulas more manageable.
Reusing Names and Chaining
A name can be defined with a calculation, and later names can refer to earlier names. This lets a formula read like a series of steps.
=LET(x, 10, y, x * 2, x + y) ' returns 30
Here x is 10, y is x * 2 (20), and the
final calculation adds them to produce 30. The order matters: a name may only
refer to names defined before it. Referring to a later name returns a
#NAME? error.
LET with Ranges and Arrays
Names can hold entire ranges or arrays, which is useful when the same range is needed more than once. This example calculates a weighted average.
| A | B | C |
|---|---|---|
| 80 | 0.3 | |
| 90 | 0.5 | |
| 70 | 0.2 | |
| =LET(scores, A1:A3, weights, B1:B3, SUM(scores*weights)) |
This table demonstrates using LET to name ranges and avoid
repeating them in the calculation. The formula computes a weighted average of
scores.
=LET(scores, A1:A3, weights, B1:B3, SUM(scores*weights))
This formula assigns ranges to the names scores and
weights, then calculates the weighted sum. The result is
(80*0.3 + 90*0.5 + 70*0.2) = 83. LET makes such formulas more
readable and avoids typing each range twice.
A name can also hold a single cell used in a geometric calculation. If A1 contains 3, the following formula returns the area of a circle with radius 3, which is about 28.27.
=LET(radius, A1, PI() * radius ^ 2)
LET with Conditional Logic
LET can combine with IF to create readable
conditional formulas. This example determines a discount based on purchase
amount.
=LET(total, A1,
discount, IF(total>100, 0.2, IF(total>50, 0.1, 0)),
total * (1 - discount))
This formula assigns the purchase amount to total, calculates the
appropriate discount, then applies it. For A1=120, the discount is 0.2 and the
result is 120 * (1 - 0.2) = 96. LET makes the logic clear by
separating the steps.
LET for Performance Optimization
LET improves performance by calculating a value once and reusing
it. This example shows how it avoids a redundant calculation.
| A | B |
|---|---|
| 100 | |
| 200 | |
| =LET(avg, AVERAGE(A1:A2), COUNTIF(A1:A2, ">"&avg) + COUNTIF(A1:A2, "<"&avg)) |
The table shows how LET calculates the average once and reuses it,
rather than computing it twice in the COUNTIF functions. This
optimization becomes more valuable with complex calculations.
=LET(avg, AVERAGE(A1:A2),
COUNTIF(A1:A2, ">"&avg) + COUNTIF(A1:A2, "<"&avg))
This formula calculates the average of A1:A2 (150) once as avg,
then counts the values above and below it. The result is 2, because 100 is
below 150 and 200 is above it. Without LET, AVERAGE
would be calculated twice.
LET with Dynamic Arrays
LET works well with dynamic array functions in modern Excel. This
example filters and processes data efficiently.
=LET(data, FILTER(A1:A10, B1:B10="Yes"),
avg, AVERAGE(data),
STDEV.P(data)/avg)
This formula filters the values where B1:B10 equals "Yes", calculates their
average, then computes the coefficient of variation. LET makes
each step clear and avoids repeating the FILTER operation.
LET vs Helper Columns
Before LET, a multi-step calculation usually required extra helper
columns. The table below uses a helper column C for the profit margin and
column D for the rating.
| A | B | C | D |
|---|---|---|---|
| Revenue | Cost | Margin | Rating |
| 1000 | 700 | =(A2-B2)/A2 | =IF(C2>0.25,"Good","Low") |
With LET, both steps live in one cell and no helper column is
needed.
=LET(revenue, A2,
cost, B2,
margin, (revenue - cost) / revenue,
IF(margin > 0.25, "Good", "Low"))
The margin is (1000 - 700) / 1000 = 0.3, which is greater than 0.25, so the formula returns "Good". The intermediate value never touches the worksheet, which keeps the sheet cleaner and prevents the helper cells from being changed by mistake.
LET with LAMBDA
LET and LAMBDA complement each other. A
LAMBDA turns a formula into a reusable named function, while
LET keeps the inside of that function readable. This example
creates a named function that returns the profit margin.
Name: MARGIN Refers to: =LAMBDA(revenue, cost, LET(profit, revenue - cost, profit / revenue)) =MARGIN(1000, 700) ' returns 0.3 =MARGIN(250, 300) ' returns -0.2
Inside the LAMBDA, LET stores the profit in a name
and then divides it by the revenue. For 1000 and 700 the result is 0.3, and for
250 and 300 it is -0.2. The same idea works with an anonymous
LAMBDA that is called immediately.
=LAMBDA(revenue, cost, LET(profit, revenue - cost, profit / revenue))(1000, 700)
LET Errors and Troubleshooting
| Cause | Symptom | Fix |
|---|---|---|
| Calculation argument is missing (even number of arguments) | #VALUE! error | Always end the formula with the calculation; the argument count must be odd |
Name looks like a cell or R1C1 reference, such as A1 or
c |
Formula is rejected or returns #VALUE! | Choose a name that is not a valid reference, such as count1 |
| Name contains a space or a period | Formula is rejected | Use only letters, numbers, and underscores |
| Calculation refers to a name defined later | #NAME? error | Define each name before it is used; names only see earlier names |
| Name is misspelled in the calculation | #NAME? error | Match the spelling exactly; names are case-insensitive but must exist |
| Formula is opened in Excel 2019 or earlier | #NAME? error | Use Excel for Microsoft 365, Excel 2021, Excel 2024, or Excel for the web |
| More than 126 name/value pairs | Formula is rejected | Reduce the number of variables or pass a range instead of many values |
The LET function is a powerful tool for creating readable,
efficient Excel formulas. By naming intermediate calculations, you can build
complex logic while keeping each step in one place. LET improves
both performance and maintainability, especially in large workbooks. Remember
that the calculation is always the required last argument, that names must be
valid, and that a name can only refer to names defined before it. Mastering
LET will significantly enhance your Excel formula skills.
Author
List all Excel Formulas.