Author: Oscar Cronquist Article last updated on August 26, 2021

LET function

This article demonstrates the LET function introduced in Excel 365.

1. How to use the LET function

How to use the LET function

The LET function lets you name intermediate calculation results which can shorten formulas considerably and improve performance. It can store up to 126 calculations and supports up to 126 names.

Formula in cell D3:

=LET(x, B3, y, C3, x*y)

The LET function in cell D3 gives cell reference B3 the name x and C3 the name y. The last argument is the actual formula, in this case, the asterisk multiplies x to y. 7*4 equals 28.

This example shows you how to name a cell reference, however, you can also name calculations. There is really no benefit in this example to use the LET function, it doesn't shorten the formula or speeds up the calculations. It only demonstrates how it works in a simple way.

Back to top

2. Excel Function Syntax

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

Back to top

3. Arguments

name1 Required. A name to assign that starts with a letter.
name_value1 Required. The value that is assigned to name1, can be a constant, array, cell reference, or calculation (formula).
calculation_or_name2 Required. It can be one of these things:

calculation - A calculation that uses the specified names given in the LET function.

name2 - Another name to assign.

name_value2 Optional. The value that is assigned to name2, can be a constant, array, cell reference, or calculation (formula).
calculation_or_name3 Optional. It can be one of these things:

calculation - A calculation that uses the specified names given in the LET function.

name3 - Another name to assign, name_value3 must be specified and so on.

Back to top

4. Comments

The greatest benefit of using the LET function is if you use the same expression over and over in a formula. The LET function lets you calculate the expression once and then use the result in the formula as many times as you like.

This speeds up calculations and makes the formula shorter, sometimes much shorter. See the links (section 6) below for examples.

Tip! Give intermediate calculations descriptive names, this means that you don't need to remember what cell references point to or what calculations calculate, the name tells you what you need to know.

5. Video

Back to top

6. Examples

6.1 Example 1

This example demonstrates a formula that extracts unique distinct rows (records) sorted from A to Z ignoring blank rows. The LET function names an intermediate calculation x, the calculation is used three times in the formula.

This makes the formula much smaller, much faster to calculate, and easier to read and understand.

Formula in cell E3:

=LET(x, UNIQUE(FILTER(B3:C7, (C3:C7<>"")*(B3:B7<>"")), FALSE), SORTBY(x, INDEX(x, 0, 1), , INDEX(x, 0, 2), ))

Link to article: Extract unique distinct rows sorted from A to Z ignoring blank rows

Back to top

6.2 Example 2

This example demonstrates how to extract unique rows sorted from A to Z ignoring blank rows. The LET function names an intermediate calculation that is used three times in the calculation.

This makes the formula much smaller and much faster to calculate.

Formula in cell E3:

=LET(x, UNIQUE(FILTER(B3:C8, (C3:C8<>"")*(B3:B8<>"")), , TRUE), SORTBY(x, INDEX(x, 0, 1), , INDEX(x, 0, 2), ))

Link to article: Extract unique rows sorted from A to Z ignoring blank rows

Back to top

6.3 Example 3

This example shows a formula in cell F3 that extracts duplicate values from a column and excludes given values specified in column D.

The LET function names an intermediate calculation z which is repeated four times in the formula.

Formula in cell F3:

=LET(z, FILTER(B3:B21, (COUNTIF(B3:B21, B3:B21)>1)*NOT(COUNTIF(D3:D4, B3:B21))), FILTER(z, MATCH(z, z, 0)<>SEQUENCE(ROWS(z))))

This formula can be shortened further:

=LET(x, B3:B21,z, FILTER(x, (COUNTIF(x, x)>1)*NOT(COUNTIF(D3:D4, x))), FILTER(z, MATCH(z, z, 0)<>SEQUENCE(ROWS(z))))

Link to article: Extract duplicate values without exceptions - Excel 365

Back to top

6.4 Example 4

This example demonstrates how to sort single digits from a cell range. An intermediate calculation is named x and is repeated two times in the calculation.

Formula in cell E2:

=LET(x, CONCAT(B3:B6), TEXTJOIN(, TRUE, SORT(UNIQUE(MID(x, SEQUENCE(LEN(x)), 1)))))

Back to top

Link to article: Sort and return unique distinct single digits from cell range