Overview
If you haven’t tried the LET function in your Excel formulas yet, get ready for your mind to be blown! The Excel team released the LET In 2019 along with the initial set of dynamic array functions (commonly known as DAFs). DAFs allow you to build much more powerful and complex formulas that spill across multiple cells instead of just one (learn more about them here). LET helps simplify and optimize formulas, many of which might not otherwise be possible!
In this article, we’ll look at what LET is, how it works, why you should use it, some tips to use, some gotchas to watch out for, and five common examples of where you might want to use it. There’s also a bonus tool at the end! Make sure to download the example file below to see these examples in action!
What is LET?
The word “let” is a commonly-used word in many programming languages for defining variables. Variables allow you to store a value and reuse it over and over. With the LET function in Excel, these variables can refer to:
- Constant values
- Cell references/ranges
- Formula results (aka “intermediate calculations”)
Within a LET function, you can define one or more names to be used in calculations you may already be using or consider using. It’s a lot like named ranges where you can refer to the name instead of a reference, only the scope is just within the formula, not all throughout a sheet or file.
How do you use LET?
=LET(name1,value1,result)
And here’s the syntax when you need to define multiple names:
=LET(name1,value1,name2,value2,...,result)
Why should you use LET?
There are four key reasons people tend to use the LET function:
- Performance optimization – any variables you define (especially those with their own formula calculations) only have to be calculated once and they can be referenced over and over again in further calculations within your formula.
- Simplicity – reusing variables means less repetitive formula parts – quicker to architect, build, and maintain – and adds structure where you otherwise wouldn’t have it.
- Readability – defining named formula parts (and eliminating redundancy) generally makes formulas much easier to read – for yourself and for others.
- Agility – it’s much easier to define and update a calculation in one place (the variable) than multiple places all throughout the formula.
Tips
Here are a few tips for getting the most out of the LET function:
- Up to 126 name/value pairs can be defined in one LET function before the resulting calculation argument.
- Earlier-defined variables can be used by later ones, providing native nesting!
- Use consistent, descriptive names for variables for clarity (not just in one individual LET function, but throughout your workbooks).
- Use line breaks (Alt+Enter) to separate functions and arguments for clarity – some people like to do this for each name/definition to be on a separate line. Indenting also helps.
- Consider using variables even if they aren’t used multiple times – they can still help with simplicity and readability.
- Consider unused variables for comments, such as the example below where the name c_1 denotes what the name x is used for.
=LET(x,5.6, c_1,"x is the start",[calculation])
Gotchas
There are also a few gotchas to be aware of when using the LET function:
- Variables are not color-coded like repeated references and helper columns, which might make it tougher to build and troubleshoot.
- Variables are not evaluated (when examining each step/argument in a formula works) with F9 formula evaluation.
- Be careful not to accidentally create circular references with definitions – it can be easy to do this when defining names that refer to themselves.
- Try to use LET when it makes formulas less complex, not more (it’s surprisingly easy to do this!).
Examples
Example 1: Remove redundant formula parts
If your formula contains components that repeat – cell range references, functions, etc. – LET allows you to define them once and use them over and over again. Notice in the before formula that the SEQUENCE function part is repeated, but it is only defined once in the after formula.
Before:
=FILTER(
SEQUENCE($B$6-$B$5+1,1,$B$5,1),
WEEKDAY(SEQUENCE($B$6-$B$5+1,1,$B$5,1),2)>5
)
After:
=LET(
fx_a,SEQUENCE($B$6-$B$5+1,1,$B$5,1),
FILTER(
fx_a,
WEEKDAY(fx_a,2)>5
)
)
=INDEX(
WRAPROWS($A$11:.$A$100004,16),
SEQUENCE(ROWS(WRAPROWS($A$11:.$A$100004,16))),
{1,3,5,7,9,11,13,15}
)
=LET(
fx_a,WRAPROWS($A$11:.$A$100004,16),
INDEX(
fx_a,
SEQUENCE(ROWS(fx_a)),
{1,3,5,7,9,11,13,15}
)
)
=(IFERROR(MID(A8,1,FIND("h",A8)-1),0)*60)+
IFERROR(MID(A8,IFERROR(FIND("h",A8)+2,1),FIND("m",A8)-1-IFERROR(FIND("h",A8)+1,0)),0)+
(IFERROR(MID(A8,IFERROR(FIND("m",A8)+2,1),FIND("s",A8)-1-IFERROR(FIND("m",A8)+1,0)),0)/60)
=LET(
h_count,IFERROR(MID(A8,1,FIND("h",A8)-1),0),
m_count,IFERROR(MID(A8,IFERROR(FIND("h",A8)+2,1),FIND("m",A8)-1-IFERROR(FIND("h",A8)+1,0)),0),
s_count,IFERROR(MID(A8,IFERROR(FIND("m",A8)+2,1),FIND("s",A8)-1-IFERROR(FIND("m",A8)+1,0)),0),
(h_count*60)+m_count+(s_count/60)
)
=IF(
XLOOKUP(G10,$B$9:$B$58,$C$9:$C$58)="",
"",
XLOOKUP(G10,$B$9:$B$58,$C$9:$C$58)
)
=LET(
xlu,XLOOKUP(G10,$B$9:$B$58,$C$9:$C$58),
IF(xlu="","",xlu)
)
Example 5: Combining formulas and adding clarity
Another great way to leverage LET is to combine separate formulas but without making them overly complex. In this case, it is stacking two values with a spilling function. Notice in the before formulas that they are separately looking for the maximum change and the name associated with that maximum, whereas in the after formula, the steps are broken out with names and definitions, then stacked together in one formula. It’s combined yet still clear.
Before 1:
=MAX($C$8:$C$33-$B$8:$B$33)
Before 2:
=XLOOKUP(E19,$C$8:$C$33-$B$8:$B$33,$A$8:$A$33)
After:
=LET(
names,$A$8:$A$33,
changes,$C$8:$C$33-$B$8:$B$33,
data,HSTACK(changes,names),
result,TAKE(SORT(data,1,-1),1),
result
)
Bonus Example
By now you probably see the value of building the LET function into your formulas. What about updating the formulas you’ve already built? You probably don’t have time for that.
But good news – you can automate it! In the XLEV8 Excel Add-in, the Letify Formula macro can find redundant parts of your formula and apply the LET function with named variables to it. Below is a look at a messy formula before using the Letify Formula macro to it, and the cleaner formula after optimizing it with the Letify Formula macro. The screenshot below shows the data referenced in the formulas. Also check it out in the video below!
=SUMPRODUCT(((A2:A100="East")*(B2:B100="Gadget")*(C2:C100>=DATE(2025,3,1))*(C2:C100<DATE(2025,4,1)))*(D2:D100*E2:E100))+
SUMPRODUCT(((A2:A100="East")*(B2:B100="Gadget")*(C2:C100>=DATE(2025,3,1))*(C2:C100<DATE(2025,4,1)))*0)+
0*(SUM(A2:A100)+SUM(B2:B100))=LET(
fx_a, DATE(2025,3,1),
fx_b, DATE(2025,4,1),
rng_a, C2:C100,
rng_b, A2:A100,
rng_c, B2:B100,
SUMPRODUCT(((rng_b="East")*(rng_c="Gadget")*(rng_a>=fx_a)*(rng_a<fx_b))*(D2:D100*E2:E100))+SUMPRODUCT(((rng_b="East")*(rng_c="Gadget")*(rng_a>=fx_a)*(rng_a<fx_b))*0)+0*(SUM(rng_b)+SUM(rng_c))
)
Video
Summary
As you can see, LET is unique but powerful. Is it crucial for all your Excel files? Probably not. But it’s a great tool to simplify and organize the different parts of your formulas in a way that helps you build them, understand them, and update them more easily. For your more complex formulas, especially for the resource-intensive ones, consider defining variables with LET and pay it forward to your colleagues and your future self!
What kind of formulas do you think the LET function would help you with? Any great success stories already? Let us know in the comments below!
Recent Comments