LET() it be! New Excel function to avoid formula duplication

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)

00_LET_01

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))

00_LET_02

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?

One comment

Leave a Reply to Sandeep Kothari Cancel reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s