How to use the LET function
This article demonstrates the LET function introduced in Excel 365.
What is the LET function?
The LET function in Excel enables you to assign names or labels to the results of calculations. This feature allows you to store intermediate calculations, values, or define names within a formula itself. These named entities are only recognized and accessible within the scope of the LET function, similar to how variables work in programming languages.
What's on this page
1. Introduction
What is intermediate calculations in Excel formulas?
In the context of Excel formulas, intermediate calculations refer to the individual steps or sub-calculations that are part of a larger, more complex formula or calculation.
Intermediate calculations are the temporary results obtained during the evaluation of a formula, before arriving at the final result. These interim values are usually not stored anywhere, but are calculated on-the-fly within the formula itself. The LET function allows you to name these sub-calculations and reuse the assigned name in the formula which results in a smaller formula, sometimes a much smaller formula and this also speeds up calculations.
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.
2. LET Function Syntax
LET(name1, name_value1, calculation_or_name2, [name_value2, calculation_or_name3...])
| 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. |
3. Example 1
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 assigns cell reference B3 the name x and assigns 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 very simple way.
4. Example 2
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
5. Example 3
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. Example 4
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
7. Example 5
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:
x - CONCAT(B3:B6) This intermediate calculation is repeated twice in the 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 post explains how to lookup a value and return multiple values. No array formula required.
This blog article describes how to split strings in a cell with space as a delimiting character, like Text to […]
Functions in 'Math and trigonometry' category
The LET function function is one of 62 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