Exceljet

Quick, clean, and to the point

Excel LET Function

Excel LET function
Summary 
The Excel LET function makes it easier to write certain complex formulas, by making it possible to declare and assign values to variables inside a formula.
Purpose 
Assign variables inside formula
Return value 
Normal formula result
Syntax 
=LET (name1, value1, [name2/value2], ..., calculation)
Arguments 
  • name1 - First name to assign. Must begin with a letter.
  • value1 - The value or calculation to assign to name 1.
  • name2/value2 - [optional] Second name and value. Entered as a pair of arguments.
  • calculation - Calculation using assigned names and values.
Version 
Usage notes 

The LET function is meant to make it easier to write more complex formulas, by making it possible to declare and assign values to variables inside a formula. Once a variable is named it can be assigned a static value, or a value based on a calculation. This allows a formula to refer to a variable by name as many times as needed, while the value of the variable is assigned in one place only.

Variables are named and assigned values in pairs (name1/value1, name2/value2, etc). LET can handle up to 126 name/value pairs, But only the first name/value pair is required.  The calculation used to return a final result from LET always appears as the last argument to the function. 

Example #1

Below is the general form of the LET function with one variable:

=LET(x,10,x+1) // returns 11

With a second variable:

=LET(x,10,y,5,x+y) // returns 15

After x and y have been declared and assigned values, the calculation provided in the 5th argument returns 15.

Example #2

The chief benefit of the LET function is elimination of redundancy. For example, the screenshot above shows a formula that uses the SEQUENCE function to generate all dates between May 1, 2020 and May 15, 2020, which are then filtered by the FILTER function to include only weekdays. The formula in E5 is:

=LET(dates,SEQUENCE(C5-C4+1,1,C4,1),FILTER(dates,WEEKDAY(dates,2)<6))

The first argument declares the variable dates and the second argument assigns the output from SEQUENCE to dates:

=LET(dates,SEQUENCE(C5-C4+1,1,C4,1)

Notice the start and end dates come from cells C4 and C5, respectively. Once dates has been assigned a value, it can be used in the final calculation, which is based on the FILTER function:

FILTER(dates,WEEKDAY(dates,2)<6)) // filter out weekends

Notice dates is used twice in this snippet: once by FILTER, once by the WEEKDAY function. In the first instance, the raw dates from SEQUENCE are passed into the FILTER function as the array to filter. In the second instance, the dates from SEQUENCE are passed into the WEEKDAY function, which checks tests for weekdays (i.e. not Sat or Sun). The result from WEEKDAY is the logic used to filter the original dates.

Without the LET function, SEQUENCE would need to appear twice in the formula, both times with the same (redundant) configuration. The LET function allows the SEQUENCE function to appear and be configured just once in the formula.

LET is a new function available in Excel 365 only.

Download 200+ Excel Shortcuts

Get over 200 Excel shortcuts for Windows and Mac in one handy PDF.