How a tidy formula turns into a monster
The scenario uses two Excel tables. tblProducts holds product IDs, standard prices and discount rates. The orders table holds product ID, quantity and customer tier. The formula grows in four steps:
- Price lookup. A single
XLOOKUPreturns the standard price for a product ID. - Quantity and discount. The price is multiplied by quantity, then by one minus the product's discount rate, which needs a second
XLOOKUP. - Premium tier. Premium customers get five extra percentage points off. The example adds this to the standard discount. A 10% product discount becomes 15% for a premium customer. It is not compounded as 10% off and then 5% off the reduced price.
- Missing products. An unknown ID such as P999 should return a blank instead of an error. This wraps the whole thing in an
IFand repeats the price lookup.
Each change is sensible on its own. The result is a formula that looks up the same product ID three times and mixes lookups, tier logic and error handling in one expression. Phillips names two warning signs. One is repeated calculations, each a place to forget an update later. The other is layered logic you have to decode every time you reopen the workbook.
A small habit helps while you work. Phillips suggests pressing Alt+Enter to break a long formula across lines. Microsoft's documentation describes Alt+Enter as the way to insert a line break while editing inside a cell. Microsoft's page doesn't specifically cover the formula bar, so treat that detail as the author's observation. It changes nothing about how the formula calculates.
What LET actually does
Microsoft says LET assigns names to calculation results, which lets you store intermediate calculations or values inside a formula, and the names apply only within that LET function. These are not workbook-wide defined names. They exist only inside the one formula.
The structure is simple:
- You supply name and value pairs. You must define at least one pair, and LET supports up to 126.
- The last argument is the calculation that returns the result. The last parameter is the calculation, which can use the values you named.
- Microsoft's original announcement adds a handy sanity check: a properly structured LET has an odd number of arguments.
The smallest example is =LET(x, 5, SUM(x, 1)), which returns 6.
The rewrite, step by step
Phillips's first LET version names each lookup once:
Priceholds theXLOOKUPfor the standard price, returning""if the ID isn't found.DiscountRateholds the discount lookup, returning0if not found.TierDiscountis0.05for Premium customers and0otherwise.TotalDiscountisDiscountRate + TierDiscount.- The final line is
IF(Price="", "", Price * [@Quantity] * (1 - TotalDiscount)).
His second version goes further. It adds Quantity, Subtotal and FinalPrice as named steps. The variable names then read like a recipe: subtotal, total discount, final price. That is the real payoff. The formula is not shorter, and the author says so himself. Phillips argues the goal is clarity and maintainability, not brevity.
What Microsoft says about performance
Microsoft lists two benefits. If you write the same expression several times, Excel calculates it several times, while LET lets you call it by name so Excel calculates it once. Microsoft also claims better readability. Its own filter example is described as computing twice as fast as without LET.
Treat that as a documented best case from a specific example. It doesn't promise a speedup for every LET formula. In a small order table the difference will be invisible. It matters more with large ranges or expensive functions like FILTER repeated across many rows. The readability gain applies everywhere.
Version compatibility
This is the part the original piece glosses over.
- LET. Microsoft's support page lists Excel for Microsoft 365 (Windows and Mac), Excel 2024 and Excel 2021 (both Windows and Mac). How-To Geek's article also names Excel for the web. The Microsoft LET page I reviewed doesn't list the web version in its "Applies To" line. Some Microsoft Q&A answers suggest Excel for the web as a workaround, but those are community posts, not official documentation. Check it in your own environment.
- XLOOKUP. The sample formula also depends on
XLOOKUP. Microsoft states that Excel 2021 for Windows includes XLOOKUP and LET. Microsoft'sXLOOKUPpage notes it is not available in Excel 2016 or 2019. Those versions can open a workbook containing it, but they can't calculate it.
So LET availability alone does not guarantee that the example works. If you share workbooks with Excel 2016 or 2019 users, the pricing formula as written won't calculate for them.
For admins on Microsoft 365 Apps, update channels once mattered. An older Microsoft Q&A answer noted LET was not yet available in the Semi-Annual Enterprise Channel at the time. LET has long been generally available, so this mainly affects machines on very old builds. If a user sees #NAME? after typing LET, check the Excel build and update channel first.
Caveats before you copy the formula
- A blank can hide a problem.
XLOOKUP's fourth argument,[if_not_found], controls what is returned when there's no match. Without it, you get#N/A. Returning""gives a clean sheet, but a mistyped product ID will quietly produce a blank price. In a real order workflow, a visible message such as "Unknown product" may serve you better. - The blank test is narrow.
IF(Price="", ...)only catches an emptyPrice. It doesn't validate quantity or discount values. A matched product with an empty price cell would also return blank. - The discount rule is the author's. Adding the premium points to the standard rate is a business decision. If your policy compounds discounts, change
TotalDiscountaccordingly. - Helper columns still have a place. The author keeps them for results people need to see, audit or reuse elsewhere. LET suits intermediate values that serve only one result.
When to reach for LET
Use it when a formula repeats the same lookup or calculation, or when naming steps would make the logic obvious on a later read. Skip it for a short formula where names would add noise. Phillips doesn't use LET for every formula with more than a couple of functions.
A practical way to convert an existing formula:
- Find the expression that appears more than once.
- Give it a descriptive name, such as
PriceorDiscountRate, starting with a letter. Microsoft notes names must follow valid name rules, so avoid names that look like cell references. - Move each named value into a name/value pair, in the order later steps need them.
- Put the final result calculation last.
- Test against a known good row, then against edge cases like a missing ID.
Bottom line
LET isn't about showing off. It lets a formula document itself. Microsoft backs the readability and calculate-once benefits, and the pricing example shows the idea clearly. Just confirm that every function you use, especially XLOOKUP, works in the Excel versions your colleagues run. Also decide deliberately what a missing product should look like.
References
- When my Excel formulas spiral out of control, LET is my fix How-To Geek · 2026-11-16T18:30:15+00:00
- LET function | Microsoft Support support.microsoft.com
- XLOOKUP function | Microsoft Support support.microsoft.com