Excel's LET function is on the Rise
Coding complex Excel formula with the new LET function is an utter joy - and that's just one of the many reasons to use this most elegant of functions.
LET allows the writing of complex formula without having to nest functions within functions within functions, and so on, which means less time having to write then and fewer bugs. It also serves to better document your functions, making it useful even for quite simple formulae.
LET was released into Excel 365 in November 2020, and so it's not yet widely known. But this article gives six compelling reasons to use LET, which will surely propel it up the list of the most used and useful Excel functions, alongside COUNT, SUM and IF.
Â
Download Sample Workbook
A sample workbook (LET Function.xlsx) is provided to accompany this article, in which you can see two formula that are identical in logic, but one is written with the LET statement and one without. To gain the full benefit of this article you should download this workbook now.
Let Function
.xlsx
Download XLSX • 15KB
On Sheet1 you'll see a small table, of names and email addresses. And cell G2 contains a name, and data validation has been applied to this cell to ensure you can only select one o the people in the Name column. The requirement is to search for the selected person in the table, and extract the domain name (the part to the right of the @ sign) from their email address. Two different cells (G7 and G9) meet this requirement - the first written in the traditional manner, and the second with the LET function.
Cell G7 - Traditional Formula
If you select cell G7 in the Sample workbook and expand the formula bar you will see the following:
IFERROR(
RIGHT(
INDEX(
Table1[Email],
MATCH(%G$3,Table1[Name],0)
),
LEN(
INDEX(
Table1[Email],
MATCH($G$3,Table1[Name],0)
)
) -
SEARCH(
"@",
INDEX(
Table1[Email],
MATCH($G$3,Table1[Name],0)
)
)
),
"Not Found"
)This code successfully meets the requirement, but obviously has taken some considerable effort to construct. All of the functions are necessary, as is the need to include three identical INDEX/MATCH code blocks (once to extract the email address, once to find it's length, and once to find where the @ sign). The code has been written across many lined and indented in a manner suggesting the balancing of parentheses was problematic to the author.
Cell G9 - Written with the LET function
Through the expanded formula bar you can also see the code behind cell G9, which is logically identical to the traditional method above, but which benefits from having been written with the LET function:
=LET(
name, $G$3,
row, MATCH(name, Table1[Name],0),
email, INDEX(Table1[Email],row),
pos, SEARCH("@",email),
len, LEN(email),
domain, RIGHT(email, len-pos),
result, IFERROR(domain, "Not Found"),
result
)If you didn't take the time to study the traditional formula, do take some time to look at this, which will be quicker for you to understand. You will observe the following:
Six variables are declared: name, row, email, pos, len, domain and result.
Each variable is followed by a calculation that assign a value to the variable.
Variables and calculations come in pairs, separated by commas.
Variables can be use in other calculations below (but not above)
The last parameter is a calculation that delivers the result of the LET function
Note. It may appear unnecessary to have declared a variable, 'result'; and that the final parameter could, instead, be the 'IFERROR' statement. This is true, but there are advantaged in doing it this way, which we shall come to later.
Â
Six Compelling Reasons to use LET
1. No Code Duplication
In the traditional example there were three identical (and equally necessary) MATCH/INDEX blocks of code. But with the LET function each MATCH function and each INDEX function is written only once - and assigned to the names variables 'row' and 'email', which are used further down, which is obviously much easier than replicating entire code block.
2. Performance Gains
With no unnecessary code duplication there's no unnecessary code execution. So instead of executing three searches to find the email address, LET will execute one search, and then simply use the result three times. This won't be noticable in the sample spreadsheet due to its size, but if the table was larger then there would be a measurable difference, and if instead of one formula there were a formula in every row of another table, then using LET could bring about a massive performance gain.
3. Confusing Nesting is Gone
You may actually have gotten quite good at nesting, and you may be reluctant to abandon that hard-earned skill. That's fine, but now you know of the existence of LET I'm sure that in time you will eventually come to terms with that loss and enjoy instead the extra time that you will have on your hands.
4. Documented Formula
It's never been possible to add comments within the Formula bar, and we're likely to have to wait a while for that. But with LET you can chose meaningful variable names, which can go along way to helping you (and others) understand the formula at a later date. This benefit works for even relatively simple formula
For example, instead of this:
=h3*(1+H4)You can write this:
=LET( price,H3, VatRate,H4, Price*(1+VatRate) )5. Less Coding and Fewer Bugs
You have probably understood by now that with LET you will have to write fewer lines of code, and that code will be much quicker to write. Duplicated code and confusing nesting can both lead to the introduction of errors in your code, but nu using LET you can easily eliminate both these cause. Another thing that I fins helps to eliminate bugs, is the discipline of documenting as you go - and as mentioned above, the naming of variables falls into that category.
6, Find What Bugs Remain Easily
Despite the fact there will be fewer bugs, it's unlikely that you're functions will always deliver the results you expect. When that happens you will find it easier to find the problem thanks to: (a) the structure of the LET statement; and (b) the additional 'result' variable we added.
To see how this works go now to the sample worksheet, select cell G9, and in the formula bar replace the final, 'result' with, say, 'email'. The LET function will carry out the same calculations, but instead of displaying the final result, it will display whichever named variable you choose. Now you can quickly and easily test each part of the formula in turn, probably starting with the first variable declared, until you have found the problem, and when you have fixed it all you need do is reinstate, 'result', as the final parameter.
Â
Helper Cells and Columns
We cannot leave this topic without mentioning Helper cells and Helper columns. Helper means you've used a cell, or created a column in a table, in which intermediate values for use in other cells or columns, are calculated. These have evolved over decades of spreadsheet use to tackle the same problems as LET tackles. Whether you called them 'Helpers' or not, you've probably used them in the past.
The first difference is that with LET all the logic is encapsulated within a single cell, but the logic with Helpers is split between two or more cells, which makes it slightly more difficult to understand what's going on.
The second difference is that in the LET statement the variables can only be used in that statement itself, but Helper cells and columns can be references by any formula in any cell in any worksheet or, indeed, in other workbooks.
The third difference is that Helper cells take up space in your worksheet and you may not want to see them, in which case they'll need to be hidden in some way.
Â