Example 1 | Example 2 | Example 3 | Example 4 | More examples Variables are named and assigned values in pairs, separated by commas (name1,value1, name2,value2, etc). LET can handle up to 126 name/value pairs, but only the first name/value pair is required. The scope of each variable is the current LET function, and nested functions below. The final result is a calculation or a variable previously calculated. The result from LET always appears as the last argument to the function. The names used in LET must begin with a letter and are not case-sensitive. You can use names that contain numbers like “count1”, “count2”, etc., but names like “ct1” and “ct2” will fail because Excel will interpret the names as a cell reference. Space characters and punctuation symbols are not allowed in names, but the underscore character (_) can be used. The LET function is often combined with the LAMBDA function as a way to make a complex formula easier to use. LAMBDA provides a way to name a formula and re-use it in a worksheet like a custom function. Example here.
Key Benefits
The LET function provides three key benefits:
Example #1
Below is the general form of the LET function with one variable: With a second variable: After x and y have been declared and assigned values, the calculation provided in the 5th argument returns 15.
Example #2
A chief benefit of the LET function is simplification by eliminating 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: The first argument declares the variable dates and the second argument assigns the output from SEQUENCE to dates: 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: 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. For a more complex example of how the LET function can be used to eliminate redundancy in a formula, see this example.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
