Excel Cookbook

Chapter 17. LET, LAMBDA, and LAMBDA Helper Functions

Custom functions make it easier to use complex formulas because they let you call each formula by a user-friendly name. However, until recently, you could write these functions only using macros or VBA.

The LAMBDA function in Excel 365 is one of Excel’s most significant additions because it lets you write custom functions using formulas. So if you frequently use an Excel formula—such as calculating a date’s fiscal quarter—you can use the LAMBDA function to save that formula as a custom function and call it using a name of your choice.

The recipes in this chapter cover the following areas:

• How to make Excel formulas more efficient and readable using the LET function

• How to create custom functions using the LAMBDA function

• How to use Excel’s LAMBDA helper functions—functions that accept a LAMBDA argument—to perform complex operations using a single formula

17.1 Improving Formula Efficiency

Problem

You have a formula that repeats references or calculations, and you want to make the formula more efficient and readable.

Solution

Suppose you have a cell containing the formula =IFS(SUM(A:A)>50, "Large",⁠ SUM​(A:A)>30, "Medium", SUM(A:A)<=30, "Small"), which returns Large, Medium, or Small, depending on the sum of the A column. This formula refers to—and has to calculate—SUM(A:A) three times, making it inefficient.

If you’re using Excel 2021 or Excel 365, you can use the LET function to help make formulas with repeat calculations ...