How to use the LET function
This article demonstrates the LET function introduced in Excel 365.
What's on this page
1. 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:
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.
2. LET Function Syntax
LET(name1, name_value1, calculation_or_name2, [name_value2, calculation_or_name3...])
3. LET Function 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. |
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.
5. Video
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:
Original formula:
The following expression is repeated three times in the formula, I am naming it x:
x - UNIQUE(FILTER(B3:C7, (C3:C7<>"")*(B3:B7<>"")), FALSE)
Link to article: Extract unique distinct rows sorted from A to Z ignoring blank rows
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:
Original formula:
UNIQUE(FILTER(B3:C8, (C3:C8<>"")*(B3:B8<>"")), , TRUE) is repeated three times in the original formula. I am naming the intermediate expression x:
x - UNIQUE(FILTER(B3:C8, (C3:C8<>"")*(B3:B8<>"")), , TRUE)
The formula is now much shorter.
Link to article: Extract unique rows sorted from A to Z ignoring blank rows
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:
This formula can be shortened even further:
Original formula:
The following intermediate calculation is repeated four times, I am naming it z:
z - FILTER(B3:B21, (COUNTIF(B3:B21, B3:B21)>1)*NOT(COUNTIF(D3:D4, B3:B21)))
Cell reference B3:B21 is repeated four times, I am naming it x:
x - B3:B21
Link to article: Extract duplicate values without exceptions - Excel 365
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:
Original formula:
Link to article: Sort and return unique distinct single digits from cell range
Useful links
LET Function - Microsoft
Using LET function in Excel with formula examples
'LET' function examples
First, let me explain the difference between unique values and unique distinct values, it is important you know the difference […]
This article demonstrates Excel formulas that calculate complete weeks between two given dates and weeks and days between two given […]
The MEDIAN function lets you count overlapping dates between two date ranges. If you have more than two date ranges […]
Functions in 'Math and trigonometry' category
The LET function function is one of many functions in the 'Math and trigonometry' category.
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Contact Oscar
You can contact me through this contact form