There’s a new kid on the Excel function block and I’m sure it won’t have any problems finding others to play with. The function is  called LET() and can be used to define and assign a value to a variable. This variable can then be used multiple times within the braces of the LET() function.

Example:

Cell E2 has an XLookup function to return the value from B2:B4 for a match found in A2:A4. The formula goes like this:

=XLOOKUP(D2,A2:A4,B2:B4)

The cell with the result is empty, so the XLookup function returns a zero. That does not look nice and I’d rather see a “not found” instead of the zero. The approach for that is one that Excel users have been applying for many years: Wrap the formula in an IF() statement, test if the formula returns a zero and if it doesn’t repeat the formula.

This is what that looks like:

=IF(XLOOKUP(D2,A2:A4,B2:B4)=0,"not found",XLOOKUP(D2,A2:A4,B2:B4))

The result shows “not found”, but the formula repeats the XLookup calculation. With very long and complicated formulas, this approach has challenges. The calculation is duplicated, which can impact workbook performance when there are many formulas that use that approach. Also, when the formula needs to be changed, the changes must be made in each duplicate of the copy.

The new LET() function removes both of these complications.

The first parameter is the name of a new variable, MyResult. The variable is assigned a value with the second parameter. This can be a constant, like a number or a text, or like in this case, a formula.

The third parameter is a calculation that can use the variable value.

=LET(MyResult,XLOOKUP(D2,A2:A4,B2:B4),IF(MyResult=0,"not found",MyResult))

It’s also possible to assign several variables, for example like this:

=LET(A,25,B,4,A*B)

As of writing this, the new LET() function is available in Insider builds of Office 365.

Read more details about the new LET() function and how you can get it in this Microsoft support article.

What will you do with the LET() function?