A spreadsheet compares a complex discount formula with a clearer LET formula for calculating online store order prices.
Every Excel formula starts life as a reasonable idea. Then someone asks for a discount tier, a missing-data rule, and one more lookup. How-To Geek author Tony Phillips walks through exactly this slide with a small online-store workbook. His fix is Excel's LET function, which lets you give names to the pieces of a formula. This guide covers how that works, what Microsoft documents about it, and where the example needs care.

A spreadsheet compares a complex discount formula with a clearer LET formula for calculating online store order prices. 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:

  1. Price lookup. A single XLOOKUP returns the standard price for a product ID.
  2. Quantity and discount. The price is multiplied by quantity, then by one minus the product's discount rate, which needs a second XLOOKUP.
  3. 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.
  4. Missing products. An unknown ID such as P999 should return a blank instead of an error. This wraps the whole thing in an IF and 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:

  • Price holds the XLOOKUP for the standard price, returning "" if the ID isn't found.
  • DiscountRate holds the discount lookup, returning 0 if not found.
  • TierDiscount is 0.05 for Premium customers and 0 otherwise.
  • TotalDiscount is DiscountRate + 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's XLOOKUP page 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 empty Price. 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 TotalDiscount accordingly.
  • 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:

  1. Find the expression that appears more than once.
  2. Give it a descriptive name, such as Price or DiscountRate, starting with a letter. Microsoft notes names must follow valid name rules, so avoid names that look like cell references.
  3. Move each named value into a name/value pair, in the order later steps need them.
  4. Put the final result calculation last.
  5. 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

  1. When my Excel formulas spiral out of control, LET is my fix How-To Geek 2026-11-16T18:30:15+00:00
  2. LET function | Microsoft Support support.microsoft.com
  3. XLOOKUP function | Microsoft Support support.microsoft.com