## 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. Excel Function Syntax

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

## 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 |

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 |

## 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

### 'LET' function examples

The following 33 articles contain the LET function.

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 […]

This article demonstrates a scoreboard, displayed to the left, that sorts contestants based on total scores and refreshes instantly each […]

The following image shows you a data set in columns B and C. The formula in cell E2 extracts a […]

This webpage demonstrates formulas that merge three non-adjacent cell ranges. What's on this webpage Extract a list of duplicates from […]

Cell range B2:E11 contains values, the formula in cell B15 extracts unique distinct values in B2:E11, ignores blanks, and returns […]

The array formula in cell B3 extracts overlapping dates based on the date ranges in columns D and E. What's […]

This article demonstrates formulas that extract duplicate values from a column, however, column D contains values that you don't want […]

The array formula in cell D3 returnsÂ the the length of longest consecutive sequence of a value in column A. Â Cell […]

This article demonstrates formulas that extract sorted unique distinct values from a cell range containing also blanks. Unique distinct values […]

This article demonstrates two formulas that extract distinct values from a filtered Excel Table, one formula for Excel 365 subscribers […]

This article demonstrates formulas and Excel tools that extract duplicates based on three conditions. The first and second condition is […]

The formula in cell B8, shown above, extracts dates not included in the specified date ranges, in other words, dates […]

Excelxor is such a great website for inspiration, I am really impressed by this post Which numbers add up to […]

I found an old post that I think is interesting to write about today. Think of two overlapping ranges, it […]

This formula decodes a URL-encoded string, replacing specific percentage symbol (%) and a hexadecimal number with non-alphanumeric characters. Excel 365 […]

The image above demonstrates a formula that calculates tiered values based on a tier table and returns a total. This […]

This article demonstrates a formula that extracts unique values from a column also considering upper and lower characters (case sensitive). […]

Question: How to extract email addresses from this sheet? Answer: It dependsÂ on how the emails are populated in your worksheet? […]

Cell B3 contains a few odd characters and the formula in C3 shows the ANSI equivalent of each character in […]

The worksheet above shows four different time ranges in column B and C, the formula in cell C10 counts the […]

This article demonstrates how to calculate dates in a given date range (cells B13 and B14) that don't overlap the […]

This article demonstrates two formulas, they both accomplish the same thing. The Excel 365 formula is much smaller and is […]

This article demonstrates two different formulas, one for Excel 365 and one for earlier versions. Table of Contents Reverse a […]

This article demonstrates a formula that filters unique distinct single digits from a cell range containing numbers. Cell range B3:B6 […]

This article demonstrates a formula that sorts items arranged horizontally based on the adjacent numbers, every other column contains a […]

This article describes a formula that sorts values arranged in a column from A to z by every other value. […]

This article demonstrates two ways to calculate expenses evenly split across multiple people. The first one is a formula solution, […]

This article explains how to build an array formula that sums numerical ranges. Example, I want to know how to […]

This blog article describes how to split strings in a cell with space as a delimiting character, like Text to […]

This article demonstrates how to sort a table based on count meaning the formula counts each record and returns a […]

What's on this page Unique distinct values sorted based on frequency Unique distinct values sorted based on frequency - Excel […]

## Functions in this article

### 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 signsUse 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 OscarYou can contact me through this contact form