=LET(total,SUM(B2:B6),IF(total>500,total*0.9,total)) adds up B2:B6 once, names the result total, and then uses that name twice: once to test it and once to return it. Without LET you would write the SUM three times: =IF(SUM(B2:B6)>500,SUM(B2:B6)*0.9,SUM(B2:B6)).
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Order | Amount | Total to pay | |
| 2 | A-101 | 120 | 580.5 | |
| 3 | A-102 | 95 | ||
| 4 | A-103 | 210 | ||
| 5 | A-104 | 80 | ||
| 6 | A-105 | 140 |
The orders add up to 645, which is over 500, so D2 shows 580.5. Change B4 to 50 and the total drops to 485, under the limit, so D2 shows 485 with no discount.
LET syntax
=LET(name1, value1, [name2, value2, ...], calculation)
- Each
nameis a word you choose, followed by thevalueit stands for: a number, a range, or a formula. - You can define up to 126 pairs. A value may use any name defined before it.
- The last argument is the
calculation, which uses the names and gives the result.
LET needs Excel 2021, Excel 2024 or Microsoft 365. In Excel 2019 and older it shows #NAME?. Google Sheets has LET with the same syntax.
LET with several variables
Names make a per-row formula read like a sentence. Here each row works out a subtotal and takes 10% off when it passes 100:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Item | Price | Qty | To pay |
| 2 | Pen | 2.5 | 10 | 25 |
| 3 | Bag | 40 | 3 | 108 |
| 4 | Lamp | 35 | 2 | 70 |
| 5 | Mug | 8 | 15 | 108 |
| 6 | Desk | 150 | 1 | 135 |
The bag's 120 becomes 108, the mugs' 120 also becomes 108, and the desk's 150 becomes 135; the pens (25) and lamps (70) stay as they are. The formula was written in D2 and filled down, and subtotal means a different number in each row.
A name can build on the one before it: =LET(x,2,y,x*3,x+y) sets x to 2, y to 6, and returns 8.
Calculate a lookup once and reuse it
A lookup that appears twice in an IF runs twice. LET runs it once. This returns the price plus 20%, or a message when the product is missing:
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Product | Region | Price | Look for | Price + 20% | |
| 2 | Apple | North | 1.2 | Pear | 1.8 | |
| 3 | Pear | South | 1.5 | Kiwi | Not found | |
| 4 | Carrot | East | 0.8 | |||
| 5 | Bread | North | 2.4 | |||
| 6 | Milk | South | 1.1 |
Pear is found, so F2 shows 1.8. Kiwi is not in the list, XLOOKUP returns the empty text given as its fourth argument, and F3 shows Not found. Type Kiwi into A4 and F3 changes to its price plus 20%.
LET with FILTER
LET is most useful with dynamic arrays, where a filtered range is needed more than once. This names the matching rows, counts them, and averages their sales in one formula:
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Name | Region | Sales | Region | North | |
| 2 | Ann | North | 120 | Summary | 3 reps, average 126.7 | |
| 3 | Ben | South | 80 | |||
| 4 | Cara | North | 200 | |||
| 5 | Dan | East | 150 | |||
| 6 | Eve | South | 95 | |||
| 7 | Finn | North | 60 |
For North it shows "3 reps, average 126.7". Pick South in F1 for "2 reps, average 87.5". INDEX(rows,0,3) takes the third column of the filtered rows. Without LET, the FILTER would be written twice.
A month calendar with blanks
The calendar on the SEQUENCE page shows days of the neighbouring months. LET names the first of the month and the grid of dates, then blanks every date outside the month:
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Sun | Mon | Tue | Wed | Thu | Fri | Sat |
| 2 | 1 | 2 | 3 | 4 | |||
| 3 | 5 | 6 | 7 | 8 | 9 | 10 | 11 |
| 4 | 12 | 13 | 14 | 15 | 16 | 17 | 18 |
| 5 | 19 | 20 | 21 | 22 | 23 | 24 | 25 |
| 6 | 26 | 27 | 28 | 29 | 30 |
Change DATE(2026,4,1) to DATE(2026,5,1) and the grid redraws for May. Because the first of the month is named once, there is only one date to change, not three.
Practice: shipping with LET
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Order | Amount | To pay | |
| 2 | A-101 | 120 | ||
| 3 | A-102 | 95 | ||
| 4 | A-103 | 210 | ||
| 5 | A-104 | 80 | ||
| 6 | A-105 | 140 |
Your turn: In D2, total the amounts in B2:B6. If the total is under 800, add 25 for shipping; otherwise return the total.
LET naming rules and errors
Most LET errors come from the names:
- A name must start with a letter and contain no spaces.
unit pricefails; useunit_priceorunitPrice. - A name cannot look like a cell address.
a1andtax2026are cell addresses in Excel (TAX is a column), so LET rejects them with #NAME?.rateortax_2026work. - A name is spelled exactly the same each time it is used. A typo where it is used gives #NAME?, because Excel looks for a function or named range of that spelling.
- The last argument must be a calculation.
=LET(x,5)has a name and a value but nothing to return, so Excel refuses it. - Names exist only inside their own formula. To reuse a calculation in many cells, save it as a named LAMBDA instead.
=LET(a1,5,a1*2) #NAME? (a1 is a cell address)
=LET(rate,8%,100*rate) 8
Frequently Asked Questions
What does the LET function do in Excel?
It gives names to values inside one formula. =LET(total,SUM(B2:B6),total*2) calculates SUM(B2:B6) once, calls it total, and returns total times 2. The names exist only inside that formula.
How do I use more than one variable in LET?
Add more name and value pairs before the final calculation: =LET(price,B2,qty,C2,price*qty). A later value can use an earlier name, as in =LET(x,2,y,x*3,x+y), which returns 8.
Why does my LET formula return #NAME?
Either your Excel has no LET (it needs Excel 2021 or Microsoft 365), a name is misspelled where it is used, or a name is not allowed, for example one that looks like a cell address such as a1 or tax2026, or one that starts with a number.
Does LET make Excel formulas faster?
It can. Without LET a repeated expression, such as the same XLOOKUP written twice in an IF, is calculated each time it appears. With LET it is calculated once and the result is reused.
