Understanding the LET Function

Written by Allen Wyatt (last updated August 12, 2023)
This tip applies to Excel 2021 and Excel in Microsoft 365


The LET function provides a very powerful way for you to create variables that you can use within a formula. This may sound strange, but it is quite easy to do. Consider the following very simple example:

=LET(c,7,m,8,c*m)

There are three parts in this example. The first two parts define two variables named c and m and assigns values to those variables. So, in this example c is assigned the value 7 and m is assigned the value 8. The third part of the example is a formula that uses the variables, multiplying c times m. This is the value returned by the formula—56.

You can also, though, use variables within the definition of other variables. That is shown through this very small change to the previous example:

=LET(c,7,m,c+2,c*m)

In this case, c is still assigned the value of 7, but m is assigned the value of c plus 2, which works out to be 9. Finally, the multiplication produces a result of 63. Usage of variables within definitions of other variables must be done in order, meaning that you can only use variables that were previously defined within the LET function. As an example, the following will not work because when a value is being applied to the c variable, the m variable has yet to be defined:

=LET(c,m+2,m,8,c*m)

Remember that these are very simple examples, provided just so you can see how you can create variables, assign variables to them, and then use them in an equation. The important thing is that variables and values must be defined as pairs. You must define at least one such pair in the LET function, and you cannot define more than 126 such pairs. (That would make a very long formula, as well.)

For me, the real power of the LET function comes into play if you have formulas that repeat things over and over again. As an example, consider the following formula, which determines the first Friday within a range of dates specified in cells A2 and A3. (The formula is discussed more fully in this ExcelTip.)

=IF(A2+IF(WEEKDAY(A2)<=6,6-WEEKDAY(A2),6)>A3,
"",A2+IF(WEEKDAY(A2)<=6,6-WEEKDAY(A2),6))

If you rewrite this formula to utilize the LET function, then you can make it shorter:

=LET(w,WEEKDAY(A2),IF(A2+IF(w<=6,6-w,6)>A3,"",A2+IF(w<=6,6-w,6)))

All it does is to assign to the variable w the result of applying the WEEKDAY function to the date value in cell A2. This variable (w) is then used in the formula, resulting in a much shorter formula.

Here's another example of a formula that can be used to check the last two characters in a cell and, if they are equivalent to compass directions (NE, SE, NW, or SW), then make sure those are shown as uppercase. (This formula is discussed more fully in this tip.)

=IF(LOWER(RIGHT(A1,3))=" ne", LEFT(A1,LEN(A1)-2) & "NE",
IF(LOWER(RIGHT(A1,3))=" se", LEFT(A1,LEN(A1)-2) & "SE",
IF(LOWER(RIGHT(A1,3))=" nw", LEFT(A1,LEN(A1)-2) & "NW",
IF(LOWER(RIGHT(A1,3))=" sw", LEFT(A1,LEN(A1)-2) & "SW", A1))))

Here's how you could use the LET function to shorten the formula:

=LET(sDir,LOWER(RIGHT(A1,3)),sPart,LEFT(A1,LEN(A1)-2),
IF(sDir=" ne", sPart & "NE", IF(sDir=" se", sPart & "SE",
IF(sDir=" nw", sPart & "NW", IF(sDir=" sw", sPart & "SW", A1)))))

In this case two variables are defined (sDir and sPart) and another important thing is demonstrated—the LET function will work just fine with assigning text values to the variables.

The bottom line is that LET can be used to refashion very long formulas into much shorter formulas and make them easier to understand and maintain.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (9810) applies to Microsoft Excel 2021 and Excel in Microsoft 365.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Inserting Footnotes

Footnotes appear at the bottom (or foot) of each page. It is an easy task to insert a footnote at any point you desire, ...

Discover More

Creating an Excel Workbook from within Word

Word and Excel are both programs within the Microsoft Office suite. If you use both of these programs often, you may ...

Discover More

Modifying the Backup Copy File Name

Backup files, created automatically by Word, have the filename extension WBK and start with the words "Backup of." If you ...

Discover More

Best-Selling VBA Tutorial for Beginners Take your Excel knowledge to the next level. With a little background in VBA programming, you can go well beyond basic spreadsheets and functions. Use macros to reduce errors, save time, and integrate with other Microsoft applications. Fully updated for the latest version of Office 365. Check out Microsoft 365 Excel VBA Programming For Dummies today!

More ExcelTips (ribbon)

Leaving a Cell Value Unchanged If a Condition Is False

Ever want the IF function to only return a value if the condition it is testing is true, and not if the condition is ...

Discover More

Ways to Concatenate Values

Users of the most recent versions of Excel have four different ways available to combine values into strings. Even those ...

Discover More

Indirect References to a DSUM Parameter

Indirect references can be very helpful in formulas, but getting your head around how they work can sometimes be ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] (all 7 characters, in the sequence shown) in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 7 + 1?

There are currently no comments for this tip. (Be the first to leave your comment—just use the simple form above!)


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.