Get ahead before the holidays. Individual webinars are $149 throughout October. *Pricing automatically applied in cart.

Advanced Excel Formulas with LET and LAMBDA

Access this expert-led webinar instantly, available anytime on-demand.

Included in All-Access Membership
Live Webinar - no upcoming date
Customer Satisfaction Guarantee Learn with confidence. If you're not happy, we'll make it right. That's our guarantee.

Purchase Options

Select an attendee quantity to add to cart.

Recorded Webinar Only

$219.00 $149.00
or

All Access Membership

The Aurora All Access Membership is designed to provide you with the training that you want when you want it. You will have 100% access to every live webinar, on demand webinar, professional alert, and podcast that Aurora Training Advantage offers with no additional cost.

Learn More About Our All Access Membership
$599.00
All Access Membership

In this presentation, author and Excel expert David H. Ringstrom, CPA, will cover a range of topics to enhance your Excel skills. You will learn how to assign names to cells, which lays groundwork for the LET and LAMBDA functions. The LET function is available in Excel 2021 and Excel for Microsoft 365 and enables you to create pairs of names and associated values that can store intermediate calculations. David will address common issues such as the #NAME? error with the LET function and then introduce the LAMBDA function available in Excel for Microsoft 365, which enables you to create custom, reusable worksheet functions without writing programming code. He will also demonstrate the ISOMITTED function and showcase the Excel Labs Add-In. Additionally, you will discover how to save LAMBDA functions to GitHub and download them from there. David will guide you through the advanced formula environment and provide insights on moving LAMBDAs between workbooks. Don't miss this opportunity to expand your Excel expertise.

David is the author of “Exploring Microsoft Excel's Hidden Treasures: Turbocharge your Excel proficiency with expert tips, automation techniques, and overlooked features”. He demonstrates every technique at least twice: first, on a PowerPoint slide with numbered steps, and second, in the subscription-based Excel for Microsoft 365. David draws your attention to any differences in Excel 2021, 2019 or 2016 during the presentation and in his detailed handouts. The handouts include an Excel workbook with most of the examples he uses during his demonstrations. Excel for Microsoft 365 is a subscription-based product that receives periodic feature updates. Conversely, perpetually licensed versions have year numbers in their names and do not receive any feature updates.


Topics typically covered:

  • Matching on two or more columns of criteria at once with XLOOKUP.
  • Exploring the XLOOKUP worksheet function in Excel 2021 and Microsoft 365.
  • Assigning names to cells to streamline formulas and bookmark key inputs within a workbook.
  • Streamline formula writing with by creating nicknames for repetitive values with the LET function in Microsoft 365.
  • Documenting IF functions by using LET to assign nicknames to the inputs.
  • Utilizing the ISOMITTED function with LAMBDA to create optional arguments.
  • Navigating directly to inputs by using Excel's Name Box, and then returning to the previous location in the workbook via the Go To commmand.
  • Saving LAMBDA functions to GitHub for easier distribution and maintenance.
  • Create your own custom worksheet functions without writing any code in Microsoft 365 with the LAMBDA function.
  • Moving LAMBDA functions between workbooks.
  • Understanding how using names in the wrong order can result in a #NAME? error with the LET function.
  • Installing the free Excel Labs Add-In which enables API access to ChatGPT.

Learning objectives:

  • State the purpose of the column_index_num argument within VLOOKUP.
  • Recall the section of the Excel Options dialog box where the AutoRecover setting resides that controls how often Excel creates a back-up copy of your workbooks.
  • State what SUMIF returns if a match cannot be found.
  1. Introduction
  2. Presentation Overview 00:01:30
  3. Presenting with Microsoft 365 for Windows 00:04:43
  4. Section 1: Working with Named Ranges 00:07:20
  5. Using the Define Name Feature 00:08:57
  6. Create Names from Selection 00:19:15
  7. Apply Names to Formulas 00:23:43
  8. Diagnosing Broken Names 00:31:31
  9. Section 2: Introducing the LET Function 00:36:30
  10. Calculating Volume with LET (Excel 2021+) 00:37:50
  11. Streamlining IF Functions with LET (Excel 2021) 00:44:12
  12. Documenting IF with LET Function (Excel 2021+) 00:50:40
  13. #NAME? Error with LET Function (Excel 2021+) 00:53:57
  14. Section 3: Mastering XLOOKUP 01:00:47
  15. Exploring XLOOKUP (Excel 2021+) 01:01:41
  16. Matching Multiple Criteria with XLOOKUP 01:07:25
  17. Section 4: Diving into LAMBDA 01:14:17
  18. Calculating Volume with LAMBDA (Microsoft 365) 01:15:30
  19. Naming a LAMBDA Function (Microsoft 365) 01:20:15
  20. Employing Custom LAMBDA Functions 01:22:30
  21. Moving LAMBDAs Between Workbooks 01:25:16
  22. Section 5: Expanding with Advanced LAMBDA Tools 01:30:27
  23. ISOMITTED Function (Microsoft 365) 01:32:26
  24. Creating MULTILOOKUP with LAMBDA 01:38:15
  25. Section 6: Exploring Excel Labs 01:43:00
  26. Excel Labs Add-In 01:43:51
  27. Presentation Summary 01:46:29
  28. Now It’s Your Turn—But I’m Here If You Need Me! 01:47:18

  • David H. Ringstrom, CPA

    David H. Ringstrom, CPA

    David H. Ringstrom, CPA, is a nationally recognized instructor who leads dozens of Excel webinars each year. He is the author of Microsoft 365 Excel for Dummies and several other books. With over 30 years of consulting and teaching experience he empowers users to work more efficiently in E [...]

    View Full Profile

CPE Credit

Continuing Professional Education

Aurora Training Advantage is registered with the National Association of State Boards of Accountancy (NASBA) as a sponsor of continuing professional education on the National Registry of CPE Sponsors. State boards of accountancy have final authority on the acceptance of individual courses for CPE credit. Complaints regarding registered sponsors may be submitted to the National Registry of CPE Sponsors through its website: www.nasbaregistry.org.

For more information regarding administrative policies such as complaint and refund, and cancellation please contact our offices at 407-542-4317 or [email protected].

You must answer all questions during the webinar, view the recording completely and pass the test at the end with 70% correct answers to receive CPE credit.

  • #N/A Error 01:02:22, 01:11:21, 01:11:25, 01:12:10
  • #NAME? 00:08:43, 00:32:22, 00:33:38, 00:34:47, 00:35:44, 00:36:16, 00:36:21, 00:54:34, 00:57:43, 00:58:53, 01:28:29, 01:46:29
  • #VALUE! 01:26:21, 01:26:29
  • Column 00:03:58, 00:05:38, 00:11:23, 00:13:53, 00:14:10, 00:14:29, 00:14:29, 00:15:10, 00:15:33, 00:15:45, 00:19:44, 00:19:55, 00:20:06, 00:20:06, 00:20:06, 00:21:23, 00:21:23, 00:21:30, 00:22:39, 00:34:34, 00:50:58, 01:03:34, 01:04:03, 01:04:11, 01:04:28, 01:05:51, 01:06:00, 01:07:33, 01:07:33, 01:07:33, 01:07:33, 01:10:27, 01:10:27, 01:11:07, 01:11:07, 01:12:04, 01:13:19, 01:13:31, 01:13:46, 01:13:46, 01:13:52, 01:13:52, 01:32:05, 01:39:07, 01:41:48
  • Formula 00:00:08, 00:00:08, 00:00:08, 00:00:54, 00:00:54, 00:01:10, 00:01:50, 00:02:07, 00:02:17, 00:02:28, 00:02:45, 00:02:45, 00:02:55, 00:03:00, 00:03:00, 00:03:00, 00:03:14, 00:03:45, 00:03:45, 00:04:24, 00:08:02, 00:08:17, 00:08:26, 00:09:57, 00:10:57, 00:11:23, 00:11:38, 00:11:51, 00:12:34, 00:13:09, 00:13:21, 00:13:21, 00:13:44, 00:14:07, 00:14:21, 00:14:29, 00:15:45, 00:16:50, 00:17:31, 00:17:53, 00:17:58, 00:18:02, 00:18:07, 00:18:11, 00:18:26, 00:18:45, 00:18:45, 00:19:27, 00:19:27, 00:19:52, 00:21:07, 00:24:01, 00:24:16, 00:24:21, 00:24:41, 00:24:52, 00:25:11, 00:25:41, 00:25:47, 00:26:06, 00:27:46, 00:28:05, 00:28:41, 00:28:52, 00:31:19, 00:31:59, 00:32:22, 00:32:29, 00:33:19, 00:33:38, 00:33:38, 00:35:27, 00:36:08, 00:36:34, 00:39:04, 00:39:04, 00:40:25, 00:41:31, 00:42:32, 00:42:38, 00:45:43, 00:45:49, 00:46:00, 00:46:00, 00:46:55, 00:49:37, 00:50:09, 00:50:09, 00:50:32, 00:51:24, 00:52:03, 00:53:44, 00:56:28, 00:56:37, 00:57:43, 00:57:53, 00:58:13, 01:00:18, 01:01:17, 01:05:37, 01:06:26, 01:10:52, 01:16:12, 01:17:51, 01:17:58, 01:19:03, 01:20:23, 01:20:28, 01:20:28, 01:20:35, 01:21:51, 01:22:21, 01:22:21, 01:22:30, 01:22:30, 01:26:07, 01:28:00, 01:31:05, 01:33:01, 01:34:21, 01:34:36, 01:37:00, 01:37:43, 01:39:22, 01:41:48, 01:43:19, 01:43:39, 01:45:11, 01:45:22, 01:45:22, 01:45:22, 01:46:20, 01:46:29, 01:46:29, 01:46:29
  • IF Function 00:37:41, 00:45:08, 00:49:02, 00:50:40, 00:52:40
  • INDEX Function 01:00:47, 01:02:32, 01:04:28, 01:04:28, 01:05:17, 01:05:20, 01:07:07, 01:07:33, 01:11:48, 01:12:10
  • ISOMITTED Function 00:04:12, 01:31:34, 01:32:26, 01:32:36, 01:32:46, 01:33:49, 01:34:07, 01:34:11, 01:35:36, 01:35:36, 01:46:58
  • LAMBDA Function 00:00:00, 00:00:54, 00:01:16, 00:01:39, 00:02:34, 00:03:24, 00:03:58, 00:03:58, 00:04:12, 00:04:12, 00:04:24, 00:06:34, 00:07:38, 00:07:42, 00:36:21, 01:01:17, 01:14:02, 01:14:17, 01:14:29, 01:14:33, 01:15:30, 01:15:48, 01:15:51, 01:16:12, 01:16:20, 01:17:58, 01:17:58, 01:17:58, 01:17:58, 01:18:40, 01:18:42, 01:18:49, 01:18:49, 01:19:17, 01:19:26, 01:19:40, 01:19:55, 01:21:04, 01:21:14, 01:21:28, 01:21:28, 01:22:30, 01:22:30, 01:22:30, 01:23:22, 01:23:56, 01:25:37, 01:25:48, 01:26:00, 01:26:07, 01:26:29, 01:26:35, 01:26:57, 01:26:57, 01:28:29, 01:28:39, 01:29:05, 01:29:43, 01:29:54, 01:30:20, 01:30:27, 01:30:27, 01:30:27, 01:31:05, 01:31:15, 01:31:15, 01:31:34, 01:31:56, 01:32:51, 01:33:01, 01:33:39, 01:34:07, 01:34:21, 01:34:56, 01:35:36, 01:36:19, 01:37:00, 01:37:23, 01:38:24, 01:38:37, 01:40:35, 01:40:38, 01:41:48, 01:45:22, 01:45:22, 01:46:58
  • LET Function 00:01:47, 00:03:24, 00:06:34, 00:07:42, 00:36:16, 00:36:21, 00:36:30, 00:36:34, 00:36:51, 00:37:00, 00:37:18, 00:37:26, 00:37:30, 00:37:43, 00:37:50, 00:39:25, 00:41:19, 00:41:23, 00:44:12, 00:46:14, 00:48:26, 00:49:26, 00:50:29, 00:50:46, 00:52:12, 00:53:09, 00:53:29, 00:53:57, 00:54:48, 01:00:18, 01:14:22, 01:15:37, 01:16:32, 01:17:05, 01:19:49, 01:23:38, 01:46:29
  • Microsoft 365 00:00:43, 00:04:43, 00:05:01, 00:06:11, 00:06:11, 00:06:34, 00:06:34, 00:33:07, 01:18:49
  • Name Box 00:09:13, 00:10:32, 00:10:45, 00:11:57, 00:12:26, 00:19:00, 00:20:30, 00:22:30, 00:33:26, 00:33:35
  • Name Manager 00:16:59, 00:32:03, 00:32:05, 00:33:38, 01:21:14, 01:23:44, 01:26:07, 01:26:18, 01:26:21, 01:26:21, 01:28:00, 01:28:19, 01:28:29, 01:29:05, 01:29:30
  • PRODUCT Function 00:39:25, 00:40:35, 00:41:03
  • Row 00:11:23, 00:13:53, 00:14:10, 00:14:29, 00:14:29, 00:15:10, 00:15:33, 00:22:39, 01:04:03, 01:04:11, 01:05:10, 01:05:51, 01:06:00, 01:09:53, 01:10:52, 01:12:10
  • VLOOKUP 01:00:47, 01:03:17, 01:03:30, 01:03:30, 01:03:34, 01:04:03, 01:04:28, 01:04:28, 01:05:20, 01:07:33, 01:07:33, 01:10:17, 01:10:17, 01:10:26, 01:10:56, 01:13:46
  • Workbook 00:01:16, 00:01:16, 00:01:50, 00:02:17, 00:07:10, 00:07:52, 00:12:26, 00:12:31, 00:19:07, 00:20:37, 00:20:41, 00:20:52, 00:23:34, 00:32:05, 00:33:38, 00:33:38, 00:34:11, 00:37:00, 00:37:00, 01:01:35, 01:15:17, 01:15:20, 01:17:58, 01:17:58, 01:21:14, 01:23:22, 01:25:16, 01:25:22, 01:25:37, 01:25:37, 01:25:56, 01:25:56, 01:26:07, 01:26:57, 01:27:46, 01:28:39, 01:28:50, 01:28:56, 01:30:20, 01:45:22
  • Worksheets 00:00:08, 00:01:16, 00:01:30, 00:01:39, 00:07:28, 00:09:13, 00:10:40, 00:11:05, 00:11:05, 00:11:38, 00:11:57, 00:12:34, 00:12:34, 00:12:57, 00:20:41, 00:23:43, 00:24:59, 00:25:11, 00:25:11, 00:25:11, 00:25:24, 00:26:34, 00:26:43, 00:26:48, 00:27:02, 00:27:08, 00:27:38, 00:27:46, 00:27:46, 00:27:46, 00:28:16, 00:28:41, 00:29:05, 00:29:29, 00:29:36, 00:31:04, 00:31:04, 00:32:29, 00:32:44, 00:37:00, 00:37:18, 00:50:02, 00:50:21, 01:01:17, 01:06:38, 01:14:33, 01:14:33, 01:14:53, 01:15:00, 01:15:05, 01:20:44, 01:22:30, 01:24:11, 01:24:34, 01:25:48, 01:26:50, 01:26:57, 01:27:46, 01:28:39, 01:28:50, 01:29:05, 01:29:05, 01:29:30, 01:30:07, 01:43:35, 01:46:58
  • XLOOKUP 00:03:24, 00:03:24, 00:03:24, 00:03:45, 00:03:45, 00:03:58, 01:00:47, 01:01:10, 01:01:17, 01:01:41, 01:02:18, 01:02:32, 01:05:40, 01:05:40, 01:06:12, 01:07:03, 01:07:15, 01:07:25, 01:08:05, 01:12:42, 01:12:42, 01:13:40, 01:13:56, 01:20:48, 01:20:48, 01:20:52, 01:22:05, 01:32:01, 01:32:05, 01:38:20, 01:38:31, 01:38:53, 01:39:22, 01:39:22, 01:39:22, 01:40:24
  • Absolute Reference 00:11:23, 00:13:21, 00:13:53, 00:14:07, 00:14:26, 00:14:29, 00:16:31, 00:50:58, 00:52:20, 00:52:29
  • Relative Reference 00:13:21, 00:14:29, 00:15:45
  • Mixed Reference 00:14:10, 00:14:29, 00:15:10, 00:15:33
  • #DIV / 0! 00:44:37, 00:47:10, 00:47:40
  • Cell Reference 00:02:07, 00:06:34, 00:12:34, 00:13:44, 00:13:53, 00:16:10, 00:17:49, 00:22:13, 00:22:54, 00:24:01, 00:25:24, 00:27:08, 00:28:36, 00:28:38, 00:31:37, 00:40:42, 00:44:03, 00:48:52, 00:50:46, 01:02:32, 01:02:41, 01:02:47, 01:24:11, 01:30:02
  • Dynamic Array Function 00:50:32
  • Formula Bar 00:00:54, 00:28:05, 00:32:29, 00:35:27, 01:20:28
  • IF Function 00:37:41, 00:45:08, 00:46:32, 00:49:02, 00:50:40, 00:50:58, 00:52:40, 00:52:49, 00:53:50
  • IFERROR Function 00:44:21, 00:44:49, 00:47:17, 00:47:17
  • IFNA Function 01:07:07, 01:07:55, 01:11:25, 01:11:30, 01:12:10
  • ISERROR 00:45:26, 00:46:28, 00:46:32, 00:47:56, 00:49:02

#DIV / 0!: Microsoft Excel shows the #DIV/0! error when a number is divided by zero (0). It happens when you enter a simple formula like =5/0, or when a formula refers to a cell that has 0 or is blank, as shown in this picture.

#N/A Error: Excel displays this error when a lookup function, such as VLOOKUP or MATCH, cannot return the requested information.

#NAME?: When your formula has a reference to a name that is not defined in Excel, you will see the #NAME? error.

#VALUE!: #VALUE is Excel's way of saying, "There's something wrong with the way your formula is typed. Or, there's something wrong with the cells you are referencing." The error is very general, and it can be hard to find the exact cause of it.

Absolute Reference : Absolute references in Excel are a direct link to a specific cell or range of cells that remain fixed if you copy or drag the formula. Absolute references are represented by $ symbols. A $ before a column letter freezes the column, while a $ before the row number freezes the row number. You can freeze the column letter and/or row number when needed.

Cell Reference: A cell reference refers to a cell or a range of cells on a worksheet and can be used in a formula so that Microsoft Office Excel can find the values or data that you want that formula to calculate. There are three types: Relative, Absolute, and Mixed

Column: A column is a vertical series of cells in a chart, table, or spreadsheet in Excel.

Dynamic Array Function: Dynamic Arrays will make certain formulas much easier to write. You can now filter matching data, sort, and extract unique values easily with formulas. Dynamic Array formulas can be chained (nested) to do things like filter and sort. Formulas that return more than one value will automatically spill.

Formula: A formula is an expression which calculates the value of a cell.

Formula Bar: A toolbar at the top of the Microsoft Excel spreadsheet window that you can use to enter or copy an existing formula into cells or charts. It is labeled with function symbol (fx). By clicking the Formula Bar, or when you type an equal (=) symbol in a cell, the Formula Bar will activate.

IF Function: Use the IF function, one of the logical functions, to return one value if a condition is true and another value if it's false. So an IF statement can have two results. The first result is if your comparison is True, the second if your comparison is False.

IFERROR Function: Introduced in Excel 2007, the IFERROR function simplifies crafting formulas that may sometimes return an error, such as #N/A.

IFNA Function : Introduced in Excel 2013, the IFNA function allows users to display alternative results for a calculation that results in a #N/A error. The IFNA function will, however, reveal other errors, such as #REF!, #NULL!, etc. IFNA isn’t backward compatible with Excel 2010 and earlier.

INDEX Function: The INDEX function can be used to return data from within a given range based on a row and/or column number that you specify.

ISERROR: The ISERROR function checks whether a value is an error and returns TRUE or FALSE. The Excel ISERROR function returns TRUE for any error type excel generates, including #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL! You can use ISERROR together with the IF function to test for errors and display a custom message or run a different calculation when found.

ISOMITTED Function: The Excel ISOMITTED function is a helper function for LAMBDA functions to allow optional arguments. Inside a LAMBDA function, ISOMITTED will return TRUE when an argument has not been provided. ISOMITTED takes just one argument, argument, which should be the name of an argument defined in the parent LAMBDA function.

LAMBDA Function: The LAMBDA function provides a way to create a custom function in Excel. Once defined and named, a LAMBDA function can be used anywhere in a workbook. LAMBDA functions can be very simple, or quite complex, stringing together many Excel functions into one formula. A custom LAMBDA function does not require VBA or macros.

LET Function: The LET function assigns names to calculation results. This allows storing intermediate calculations, values, or defining names inside a formula. These names only apply within the scope of the LET function. Similar to variables in programming, LET is accomplished through Excel’s native formula syntax.

MATCH Function: The MATCH function searches a prescribed range for specified criteria and returns a column or row number if a match is found. MATCH can be used with other functions that require a column or row number.

Microsoft 365: Microsoft 365, formerly Office 365, is a line of subscription services offered by Microsoft which adds to and includes the Microsoft Office product line.

Mixed Reference: A mixed reference in Excel is a type of cell reference different from the other two absolute and relative. We only refer to the cell's column or row in the mixed cell reference. So, for example, in cell A1 if we want to refer to only the A column, the mixed reference would be $A1.

Name Box: The Name Box is the box to the left of the formula bar that displays the cell that is currently selected in the spreadsheet. If a name is defined for a cell that is selected, the Name Box displays the name of the cell. You can use the Name Box to define a name for a selected cell as well.

Name Manager: The Excel Name Manager is specially designed to manage names: change, filter, or delete existing names as well as create new ones.

PRODUCT Function: The PRODUCT function multiplies all the numbers given as arguments and returns the product. For example, if cells A1 and A2 contain numbers, you can use the formula =PRODUCT(A1, A2) to multiply those two numbers together.

Relative References: When you reference a worksheet cell in an Excel formula, this defaults to a relative reference, meaning the column letters and row numbers change automatically when you copy or move the formula. Conversely, unless you turn on the Relative Reference feature, macros you record will capture the absolute address of any cells that you effect during the recording process. Relative References instruct the Macro Recorder to transcribe the number of rows or columns to move to next rather than to a specific cell address.

Row: A row is the range of cells that go across (horizontal) the spreadsheet/worksheet. Rows are identified by numbers e.g. row 1, row 5. Examples of use. A row might contain the headings of a table e.g. product ID, product name, price, number sold.

VLOOKUP: An Excel worksheet function that allows you to look up data from a list by specifying criteria, cell coordinates for the list, column number from which to return data, and an indication as to whether you want an exact or approximate match.

Workbook: In Microsoft Excel a workbook is a collection of one or more spreadsheets, also called worksheets, in a single file.

Worksheets: A worksheet is a collection of cells where you keep and manipulate the data. Each Excel workbook can contain multiple worksheets.

XLOOKUP: The XLOOKUP function searches a range or an array, and returns an item corresponding to the first match it finds. If a match doesn't exist, then XLOOKUP can return the closest (approximate) match. Where a valid match is not found, return the [if_not_found] text you supply.

Customer Satisfaction Guarantee
Invest in your future with confidence! Our Customer Satisfaction Guarantee eliminates all risk, letting you focus purely on mastering new skills and advancing your career. If you're not completely satisfied, we'll ensure you are. Your satisfaction is not just a promise; it's our guarantee.

Frequently Asked Questions

LET and LAMBDA are two of the most powerful modern Excel functions, fundamentally changing how advanced users build complex formulas. The LET function allows you to assign names to intermediate calculations within a formula, eliminating the need to repeat the same sub-expression multiple times. This makes formulas shorter, faster to calculate, and far easier to read and maintain—critical advantages when building complex financial models or audit-ready spreadsheets. LAMBDA takes this further by allowing you to create your own custom, reusable functions in Excel without VBA or macros. A LAMBDA function can be named in the Name Manager and called anywhere in the workbook like a built-in function, accepting arguments and returning calculated results. This is transformative for accountants and analysts who repeatedly apply the same custom calculation logic across multiple worksheets. Together, LET and LAMBDA bring a new level of abstraction and reusability to Excel formula development, reducing errors and maintenance burden in complex workbooks. Aurora Training Advantage's Advanced Excel Formulas with LET and LAMBDA webinar is designed for accounting and finance professionals ready to master these next-generation formula tools.
The LET function solves one of the most persistent inefficiencies in Excel formula writing: the need to repeat complex sub-expressions multiple times within a single formula. Before LET, if you needed to use the same calculated value in multiple parts of a formula—such as a filtered, sorted, or transformed range—you had to repeat the entire calculation each time, making formulas long, slow, and difficult to debug. LET allows you to calculate a value once, assign it a name, and reference that name throughout the rest of the formula. This produces formulas that are more readable, easier to troubleshoot, and significantly faster to recalculate in large workbooks because Excel evaluates each named variable only once. LET is especially valuable in dynamic array formulas that chain multiple functions together. The syntax follows a name-value pair pattern: LET(name1, value1, name2, value2, calculation). LET is available in Excel 365 and Excel 2021. For accounting and finance professionals building multi-step calculations in financial models, LET is an essential tool for formula clarity and performance. Aurora Training Advantage's Advanced Excel Formulas webinar covers LET in depth with practical accounting applications.
Creating a custom function with LAMBDA involves two steps: defining the function logic and saving it as a named formula. First, write the LAMBDA formula that defines your custom calculation: LAMBDA(parameter1, parameter2, formula_using_parameters). For example, a custom function to calculate a blended tax rate might be written as LAMBDA(income, rate, income * rate * 0.92). Once the formula logic is verified, open the Name Manager (Ctrl+F3), create a new name, and paste the LAMBDA formula as the 'Refers To' value. After saving, the custom function name is callable anywhere in the workbook like a built-in function: just type the name and supply the arguments. LAMBDA functions can call themselves recursively for iterative calculations, unlocking capabilities previously requiring VBA. They can also be combined with LET to assign intermediate variable names within the custom function logic. For accounting and finance teams, LAMBDA enables creating a library of firm-specific calculation functions—tax computations, amortization schedules, custom allocation logic—that are reusable, shareable, and version-controlled within Excel. Aurora Training Advantage's Advanced Excel Formulas with LET and LAMBDA webinar provides step-by-step guidance on building and deploying custom LAMBDA functions.
Both LET and LAMBDA are available in Excel 365 (Microsoft 365 subscription) and Excel 2021 (standalone perpetual license). They are not available in Excel 2019 or earlier versions, which is an important compatibility consideration for organizations where not all users are on the same Excel version. In Excel 365, both functions continue to receive updates and enhancements as part of Microsoft's ongoing development of the dynamic array engine. Workbooks containing LET or LAMBDA formulas will display errors when opened in older versions of Excel that do not support them, making version compatibility planning essential for shared workbooks. Organizations that standardize on Excel 365 through a Microsoft 365 subscription have the broadest access to these and other modern functions—including dynamic arrays (SORT, UNIQUE, FILTER, XLOOKUP), BYROW, BYCOL, and MAP, which work powerfully in combination with LET and LAMBDA. Accounting and finance teams considering adopting these functions should audit their Excel version landscape before rolling out LET/LAMBDA-dependent workbooks. Aurora Training Advantage's Advanced Excel Formulas webinar covers version compatibility considerations alongside LET and LAMBDA function training.
Advanced Excel formulas transform financial modeling from a maintenance-heavy, error-prone process into a robust, auditable analytical system. LET and LAMBDA enable accountants to build modular, reusable calculation logic that reduces formula redundancy and makes models easier to audit and update. Dynamic array functions—SORT, UNIQUE, FILTER, XLOOKUP—replace brittle, manually maintained lookup tables and summary schedules with self-updating outputs that respond instantly to changes in source data. Combining LAMBDA with recursive logic enables complex iterative calculations—such as circular reference-free interest computations or custom amortization schedules—without VBA. Power Query automates data import and transformation from accounting systems into model inputs, eliminating error-prone manual copy-paste steps. Structured tables with dynamic named ranges make models more maintainable as row counts change. Advanced array formulas enable multi-dimensional analyses that previously required helper columns or pivot tables. Together, these capabilities allow accountants to build models that are faster, more transparent, and easier to hand off to colleagues without extensive documentation. Aurora Training Advantage's Advanced Excel Formulas with LET and LAMBDA webinar equips accounting professionals with the skills to build next-generation financial models.