LET Function in Excel: Name the Steps of a Formula
· 7 min read
LET lets you give a name to a value or a calculation inside a formula, then use that name in the rest of the formula. Long formulas become a list of short, named steps:
=LET(name1, value1, [name2, value2, ...], calculation)- Arguments come in pairs: a name, then the value it stands for. The value can be a number, a cell, a range or a whole formula.
- Later values can use earlier names.
- The last argument is the calculation that produces the result. It is not a pair, so a LET formula always has an odd number of arguments.
LET needs Excel 2021, Excel for Microsoft 365 or Excel for the web. Excel 2019 and older show #NAME?, so if your file goes to people on older versions, keep the plain formula or use helper columns.
This LET function Excel guide shows three things LET is good for: making a formula readable, calculating a repeated part once, and naming a range so a spilled result is easy to follow.
Readability: an overtime pay formula
Five contractors, their hours for the week and their hourly rate. Hours up to 40 are paid at the normal rate, and hours above 40 at 1.5 times the rate. Column D works it out in one go, column E does the same with LET.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Contractor | Hours | Rate | Without LET | With LET | Share of pay |
| 2 | Noor | 38 | $45.00 | $1,710.00 | $1,710.00 | 15.9% |
| 3 | Felix | 46 | $40.00 | $1,960.00 | $1,960.00 | 18.2% |
| 4 | Grace | 52 | $50.00 | $2,900.00 | $2,900.00 | 26.9% |
| 5 | Hugo | 40 | $42.00 | $1,680.00 | $1,680.00 | 15.6% |
| 6 | Iris | 44 | $55.00 | $2,530.00 | $2,530.00 | 23.5% |
The plain formula in D2:
=MIN(B2,40)*C2+MAX(B2-40,0)*C2*1.5It works, but you have to decode it: MIN(B2,40) is the normal hours, MAX(B2-40,0) is the overtime, and the rest is pay. The LET version in E2 says so:
=LET(
regular, MIN(B2, 40),
overtime, MAX(B2-40, 0),
regular*C2 + overtime*C2*1.5
)Both give the same results: Noor $1,710.00 (no overtime), Felix $1,960.00, Grace $2,900.00, Hugo $1,680.00 and Iris $2,530.00. Grace's 12 overtime hours at $75 add $900 to her $2,000 base.
You can type the line breaks in the formula bar with Alt+Enter (Option+Return on a Mac). Excel keeps them, and a long formula then reads like a short list.
Check a step by returning it
The last argument decides what LET returns. To see an intermediate value, temporarily change it to one of the names:
=LET(regular, MIN(B2,40), overtime, MAX(B2-40,0), overtime)For Grace this shows 12. Once the step looks right, put the real calculation back. With a plain formula you would have to copy part of it into another cell to check it. This is the main reason LET helps when you review a formula someone else wrote, including one an AI assistant wrote.
Calculate once: a repeated lookup
The second reason to use LET is a formula that does the same work several times. Here each order line looks up a price, and orders over $500 get a 5% volume discount.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Item | Qty | Without LET | With LET | Item | Price | |
| 2 | Desk lamp | 12 | $408.00 | $408.00 | Desk lamp | $34.00 | |
| 3 | Monitor arm | 4 | $356.00 | $356.00 | Monitor arm | $89.00 | |
| 4 | Footrest | 30 | $627.00 | $627.00 | Footrest | $22.00 | |
| 5 | Whiteboard | 2 | $280.00 | $280.00 | Whiteboard | $140.00 | |
| 6 | Desk lamp | 20 | $646.00 | $646.00 |
Without LET, the price lookup appears three times: once in the test and once in each result. With LET it appears once:
=LET(
price, XLOOKUP(A2, $F$2:$F$5, $G$2:$G$5),
total, price*B2,
IF(total>500, total*0.95, total)
)Results: the 12 desk lamps come to $408, under the threshold, so no discount. The 30 footrests come to $660, so they get 5% off and show $627.00. The last line, 20 desk lamps at $680, gets the discount too and shows $646.00.
What "calculated once" means
In Excel, LET works out each named value once per calculation of the formula, then reuses the result wherever the name appears. The plain formula runs XLOOKUP three times per row.
Be realistic about the gain. On a sheet of five rows you will not notice any difference. It starts to matter with lookups over tens of thousands of rows, or a FILTER or SUMIFS over a big range used several times in one formula. The bigger benefit is usually maintenance: if the price list moves, you change one range instead of three, and you cannot forget the third copy.
For the lookup itself, see XLOOKUP in Excel. If your file must also open in older versions, how to use VLOOKUP covers the classic alternative.
Naming a range for a spilled result
A name can stand for a whole range, not just one value. F2 in the first sheet works out each contractor's share of the total pay:
=LET(pay, E2:E6, pay/SUM(pay))The result spills down F2:F6: Grace has the largest share at 26.9%, Hugo the smallest at 15.6%. Without LET the formula is =E2:E6/SUM(E2:E6), which repeats the range. If the range later changes to E2:E40, the LET version needs one edit.
LET combines well with the other dynamic array functions. A common pattern is to name a filtered list and then sort it, count it or both. The FILTER function guide and the UNIQUE function guide show the building blocks.
Rules for names
- Start with a letter, and use no spaces.
unit_priceandunitPriceboth work. - No names that look like a cell.
Q1,tax2025andabc1are all valid cell addresses (Excel has columns up to XFD), so Excel rejects them as names. - Avoid function names such as
sumormin. They are confusing to read, and descriptive names such asregularandovertimeexplain the formula for you. - Names exist only inside that formula. Another cell cannot use
pricefrom D2. For a name the whole workbook can use, define it in the Name Manager. - Order matters. A value can use any name defined before it, not after.
Common mistakes
An even number of arguments. =LET(price, XLOOKUP(...), total, price*B2) has two pairs and no final calculation, so Excel will not accept it. Add the calculation at the end, even if it is just total.
A name that is also a cell address. =LET(q1, B2*0.25, q1*4) reads as cell Q1. Use first_quarter or similar.
Leaving a debug name as the result. After checking a step by returning overtime, it is easy to forget to put the real calculation back. The sheet then shows hours instead of pay, with no error.
Expecting LET to fix the logic. LET makes a formula easier to read, which makes mistakes easier to spot. It does not catch them. A wrong denominator stays wrong after you give it a nice name, so check each step against a row you can work out by hand. To check each part on its own before you name it, paste it into the free Excel formula checker.
LET vs LAMBDA
LET names values inside one formula. LAMBDA, in Microsoft 365, goes one step further: it turns a formula into your own reusable function, which you name in the Name Manager. Most people use LET far more often. Start with LET, and reach for LAMBDA only when you copy the same LET formula into many places.
FAQ
What does the LET function do in Excel?
It assigns names to values or calculations inside a formula, then returns a final calculation that uses them. Example: =LET(regular, MIN(B2,40), overtime, MAX(B2-40,0), regular*C2 + overtime*C2*1.5).
Does LET make formulas faster?
It can. Each named value is calculated once per formula, so a lookup or a big FILTER used several times only runs once. On small sheets the difference is too small to notice.
How many names can LET have?
Up to 126 name and value pairs. In practice two to five keeps a formula readable.
Which Excel versions have LET?
Excel 2021, Excel 2024, Excel for Microsoft 365 and Excel for the web. Excel 2019 and older show #NAME?.
Can I use a LET name in another cell?
No. Names in LET exist only inside that one formula. Use the Name Manager for names the whole workbook can use.
You can practise LET on real order lines, including an audit where an AI's margin formula divided by the wrong number, on the practice page.
Practise this on a realistic dataset
Solve it yourself on one company's data, with instant feedback that points at what's wrong without giving away the answer.
- Name the steps of line revenue with LET
- Margin bands per order line with LET
- Margins above 100% that can't be rightAudit
- Orders well above average, in one LET formula