LET Function

Excel Guides / Dynamic Arrays & LAMBDA

Dynamic Arrays & LAMBDA

Give names to the pieces of a long formula so it reads clearly and calculates each piece once.

Updated 12 Oct 2026 Ā· 4 min read Ā· By Sky Dashboards

LET lets you give a name to part of a formula and reuse that name further along. Long formulas become readable, repeated calculations run once instead of several times, and fixing a mistake means changing one place. If you have ever written the same SUMIFS three times inside an IF, this is the function you want.

LET syntax

=LET(name1, name_value1, calculation_or_name2, [name_value2, calculation_or_name3], ...)

Argument Required? What it does
name1 Required The first name to define, such as total. It must start with a letter.
name_value1 Required The value, cell reference or calculation that name1 stands for.
calculation_or_name2 Required Either the final calculation, which uses the names and returns the result, or a second name to define.
name_value2 Optional The value for the second name. Keep adding name and value pairs as needed.
calculation_or_name3 Optional The final calculation, or another name. The last argument of LET is always the calculation.

Names and values come in pairs, and the last argument is the calculation that produces the result. You can define up to 126 names. Each one exists only inside that formula, so the same name can mean different things in different cells.

LET is available in Excel 2021, Excel 2024, Microsoft 365 and Excel for the web.

A first LET formula

The simplest way to see it work:

=LET(x, 5, y, 3, x * y)

Defines x as 5 and y as 3, then returns x * y, which is 15.

A name can be built from earlier names, so each step takes the previous one further:

=LET(price, 120, gst, price * 10%, price + gst)

Returns 132. gst uses price, which was defined first.

Order matters. A name can only use names defined before it, reading the formula from left to right.

Rewrite a long formula with LET

A bakery gives wholesale customers 5% off once their orders for the quarter pass $1,000. Without LET, the customer's total appears three times: once in the test, once in the discounted result and once in the full-price result.

=IF(SUMIFS(C2:C7, A2:A7, F1)>1000, SUMIFS(C2:C7, A2:A7, F1)*0.95, SUMIFS(C2:C7, A2:A7, F1))

Before: the same SUMIFS written three times.

With LET, the total is worked out once and given a name:

=LET(total, SUMIFS(C2:C7, A2:A7, F1), IF(total>1000, total*0.95, total))

After: one SUMIFS, and the logic reads almost like a sentence.

F2fx=LET(total, SUMIFS(C2:C7, A2:A7, F1), IF(total>1000, total*0.95, total))
A B C D E F
1 Customer Month Amount Customer Corner Cafe
2 Corner Cafe Jul $420.00 Net total $1,201.75
3 Hilltop Deli Jul $310.00
4 Corner Cafe Aug $455.00
5 Market Hall Aug $280.00
6 Corner Cafe Sep $390.00
7 Hilltop Deli Sep $295.00

F2 returns the net total for the customer named in F1. Corner Cafe ordered $1,265.00, so 5% off gives $1,201.75.

Change F1 to Hilltop Deli and the result is $605.00: below the threshold, so no discount.

Faster as well as shorter

In the first version Excel runs SUMIFS for the test and then again for whichever result it returns. With LET it runs once and the answer is reused. In one cell you will not notice, but copied down thousands of rows, or with FILTER and XLOOKUP inside, it adds up.

Lay out LET formulas on several lines

A LET formula reads best with one name per line. Click in the formula bar where you want a break and press Alt + Enter. Spaces at the start of a line are allowed, so you can indent too.

āŒØĀ  New line inside a formula

Alt + Enter

Works in the formula bar and when editing in the cell with F2.

=LET( total, SUMIFS(C2:C7, A2:A7, F1), discount, IF(total > 1000, 5%, 0), total * (1 - discount) )

The same bakery formula with the discount rate named as well. It still returns $1,201.75.

If the formula bar only shows one line, press Ctrl + Shift + U to expand it, or drag its bottom edge down.

Check one step at a time

To see an intermediate value, temporarily change the last argument to one of the names. Replace total * (1 - discount) with total and the cell shows $1,265.00. Put the calculation back when you are happy.

Rules for LET names

  • Start with a letter. After that, letters, numbers, underscores and full stops are allowed.
  • No spaces. Use unit_price or unitPrice.
  • Nothing that looks like a cell reference. tax1 is column TAX, row 1, so Excel rejects it. The same goes for q1 or abc10. Use tax_1 or taxRate instead.
  • Not case-sensitive. Total and total are the same name.
  • Short but meaningful. total, rate and online read better than x, y and z once the formula grows.

Note

LET names only work inside their own formula. To reuse a value across many cells, put it in a cell or create a workbook-wide name with Formulas > Define Name instead.

LET with FILTER and other array formulas

LET pays off most with dynamic arrays, where the same test or the same filtered list is often needed twice. This sorts online product names by sales and writes the channel test only once:

=LET(online, B2:B8="Online", SORTBY(FILTER(A2:A8, online), FILTER(C2:C8, online), -1))

online holds an array of TRUE and FALSE values that both FILTERs reuse. The data is in the FILTER guide.

A name can hold anything a formula can return: a number, text, a range, an array, or even a function built with LAMBDA.

Frequently asked questions

Which Excel versions have LET?

Excel 2021, Excel 2024, Microsoft 365 and Excel for the web. Excel 2019 and earlier do not recognise it and show #NAME?.

How many names can one LET formula have?

Up to 126 name and value pairs, followed by the final calculation.

Why does my LET formula return #NAME?

Usually a name is misspelt in the calculation or is used before the pair that defines it. In Excel 2019 and earlier, LET itself is unknown, which gives the same error.

Is a LET name the same as a named range?

No. A defined name lives in Name Manager and works anywhere in the workbook. A LET name exists only inside the formula that creates it.

Want the finished dashboard instead?

My Excel templates come with these formulas already wired up. Pick one, type in your own rows and the charts update.

Browse templates ā†’

Related guides

All 32 Excel guides