Excel tips: how to use the LET function

1 Apr 2021

7 min read time

At a glance

The new LET function is unusual in that it does not perform a calculation. The function allows you to create and use variables within your cell-based formulas. This function is currently only available in the subscription version of Excel.

In large files, the function can speed up calculation time by reducing the number of times a part of a formula is calculated. The speed saving is unlikely to be noticeable in smaller files.

Syntax

LET(name_1,name_value1, name_2,name_value2,calculation)

name_1 – the first variable name.

name_value1 – the value of the first variable, note this can be a cell; a range or the result of a function or formula.

name_2 and name_value2 – same as above for a second variable. You can have more than two variables.

calculation – the formula to perform a calculation using the variable(s).

Example

=LET(x,SUM(B1:B10),IF(x=0,0,B1/x))

The formula makes the variable x equal to the SUM of range B1:B10. The IF function then checks to see if x equals zero. If it does, then zero is returned, otherwise B1 is divided by x to calculate a percentage of the total. This example uses a single variable.

You can use multiple variables as per the simple example below. You pair up the variable name and its value separated by a comma. After the last comma, the calculation is performed.

=LET(x,5,y,7,x+y)

This formula returns 12. You can use more descriptive variable names as well.

Worked example for sheet name

I use sheet names in many of my models to hold a code that I want to extract or report on. As an example, a sheet named ADMIN summarises the costs for the ADMIN department. I use a formula in a cell in that sheet to extract the sheet name.

That sheet name is then used by other formulas in the sheet to create a report. I can then copy that sheet and rename it to another department code, for example MARKETING. The copied sheet’s calculations automatically update for the MARKETING department.

Since sheet names cannot be duplicated, this prevents accidental duplication of reports. It is also a scalable technique. To add a new department, just copy the department report sheet, rename it and the new department report is done.

There is a single formula that extracts the sheet name, shown in Figure 1.

As you can see, the formula is long and hard to follow. The CELL function is used three times. These characteristics are the ideal use case for the LET function.

The CELL function returns the full path of the file, including the folder path, the full file name and the sheet name on the end. An example of the CELL function output is shown in Figure 2.

Note that the formula is entered in cell A1, and it refers to cell A1. It does not return a circular reference error because the CELL function returns information – it does not perform a calculation.

The sheet name formula in Figure 1 uses the RIGHT function to extract from the right of the CELL function output string. The second argument in the RIGHT function specifies how many characters to extract. The LEN function counts the number of characters in the CELL output string. The SEARCH function returns the position number of the right square bracket.

The right square bracket is directly in front of the sheet name in the CELL function output. The difference between the LEN result and the SEARCH result is the number of characters to extract from the right of the cell. I go into more detail about how this formula works in the companion video to this article. The LET version of the formula is shown in Figure 3.

I have used x as the variable name and the CELL function as its value. Having created the variable, you substitute the variable for the complete CELL function within the RIGHT, LEN and SEARCH functions. As you can see, this creates a much shorter formula, and it is easier to follow than the original formula.

The formula in Figure 3 will work in any cell in the sheet. Note that the file must be saved for the formula to work. If you create a new blank file, the formula won’t work until you save the file.

Handling results of a XLOOKUP

If you have the LET function, you will also have the XLOOKUP function (VLOOKUP replacement), as it is also included in the subscription version of Excel.

XLOOKUP is the preferred lookup function, and it is covered in the August 2020 issue of this magazine.

When you use XLOOKUP to perform a lookup, you may need to handle different results differently.

The points below explain how we want to handle the different lookup results.

blank cell – return the text ZERO ERROR
number – return the number
text number – return the text number converted into a real number
text – return the text TEXT ERROR

In a single formula, this may mean using XLOOKUP multiple times with multiple IF functions. In the past, to make this easier, you would use a helper cell that returned the result of the XLOOKUP. You then refer to that helper cell to handle each different requirement.

In Figure 4, we have a table to look up on the left. The entries in the yellow cells are what we want to look up. Column C describes the entry in column B. Column B is what we want to extract based on the code in column A.

Column F has the XLOOKUP function result. The formula in cell F2 is

=XLOOKUP(E2,$A$2:$A$6,$B$2:$B$6)

Figure 5 shows the single formula without using a helper cell and without using the LET function. It uses same XLOOKUP function three times. This same XLOOKUP function is used by itself in cell F2 in Figure 4. The word ERROR is also used three times in Figure 5.

Figure 6 has the LET function version from cell G2 in Figures 4 and 5.

In the LET function version (Figure 6):

We capture the XLOOKUP result in the x variable.
We capture the word ERROR in the e variable. Note the leading space, which simplifies joining the word to preceding text.
We then replace each use of the XLOOKUP function with x.
The & character is used to join text together. We can convert “ZERO ERROR” into “ZERO”&e.

In both formulas (Figure 5 and 6):

The IFERROR function handles any input errors by displaying the text INPUT ERROR. This is entered at the end of the IFERROR function. Possible input errors include a blank input (yellow) cell or an invalid code in column E. See row 5 for an example of an invalid code.
The first IF function handles when the XLOOKUP function returns a blank cell from the table on the left. A blank cell in the table is returned as a zero. If that happens, the text ZERO ERROR is displayed.
The second IF function handles both numbers and text numbers. Note that multiplying a text number (like those in cells B3 and B5) by one converts them into a real number. The ISNUMBER function returns TRUE for numbers and FALSE for everything else.
The FALSE section of the second IF function handles if the XLOOKUP returns text. Note that any text numbers would have been already handled.