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
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. Please Ask Questions Today! 00:03:11
  3. Excel Versions 00:05:15
  4. Microsoft 365 Windows Beta Channel 00:05:57
  5. Microsoft 365 Mac Beta Channel 00:09:53
  6. PRODUCT Function 00:10:29
  7. IF Introduction 00:13:24
  8. IFS Function (Excel 2019+) 00:21:36
  9. CHOOSE Introduction 00:21:36
  10. SWITCH Function (Excel 2019+) 00:30:06
  11. XLOOKUP Introduction (Excel 2021+) 00:35:11
  12. XLOOKUP Matching Multiple Criteria 00:41:44
  13. Create Names from Selection 00:46:18
  14. Apply Names to Formulas 00:51:03
  15. Diagnosing Broken Names 00:52:50
  16. #NAME? Error with Cell Names 00:56:46
  17. LET Function (Excel 2021+) 00:57:17
  18. Documenting IF with LET 01:08:12
  19. Calculating Volume with LET 01:12:24
  20. #NAME? Error with LET Function 01:16:00
  21. LAMBDA Function (Microsoft 365) 01:25:28
  22. LAMBDA Function (Microsoft 365) - Steps 1 - 5 01:34:52
  23. LAMBDA Function (Microsoft 365) - Using LAMBDA To Rewrite Formulas 01:40:
  24. ISOMITTED Function (Microsoft 365) 01:40:26
  25. Advanced Formula Environment - Steps 1 - 5 01:41:19
  26. Advanced Formula Environment - Steps 6-13 01:41:57
  27. Moving LAMBDAs Between Workbooks 01:42:17
  28. Thank you for attending! 01:43:47
  29. Presentation Closing 01:43:49
  • 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 00:33:07
  • #NAME? 00:53:09, 00:57:03, 01:16:28
  • #VALUE! 00:25:47
  • Cell 00:10:59, 00:46:33, 01:42:25
  • CHOOSE Function 00:21:36, 00:27:04, 00:28:14, 00:30:32
  • Column 00:29:45, 00:36::39, 01:20:09
  • Formula 00:01:26, 00:02:09, 00:17:51, 00:46:26, 00:47:43, 00:51:54, 01:02:57, 01:11:04, 01:16:26, 01:22:01, 01:32:06
  • IF Function 00:13:25, 00:24:56, 01:08:25
  • IFS Function 00:21:36
  • INDEX Function 00:29:40, 00:59:48
  • ISOMITTED Function 01:40:26
  • LAMBDA Function 00:00:10, 00:02:12, 00:10:40, 00:35:26, 01:25:29, 01:32:16, 01:34:55, 01:41:26
  • LET Function 00:00:08, 00:01:56, 00:46:23, 00:57:21, 01:08:40, 01:12:38, 01:24:39
  • Microsoft 365 00:06:09, 00:08:45, 01:16:01
  • Name Box 00:47:26, 01:26:37
  • Name Manager 01:42:38
  • PRODUCT Function 00:10:41, 00:13:19, 01:12:33
  • Row 00:29:44, 00:52:59, 01:02:37
  • SWITCH Function 00:30:17
  • VLOOKUP 00:29:37, 00:30:56, 00:32:13, 00:41:51
  • Worksheet 00:02:14, 00:10:35, 00:46:32, 01:42:27
  • XLOOKUP 00:35:21, 01:01:12, 01:25:53

#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.

CHOOSE Function : The CHOOSE function allows you to return a specified item from a list, but in certain cases, it also can be used to have VLOOKUP return data from the left of its criteria column.

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

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

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.

IFS Function: The IFS formula in excel 2019 checks whether one or more conditions are met, and returns a value that corresponds to the first TRUE condition.

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.

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.

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.

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.

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.

SWITCH Function : The SWITCH function was introduced in the Office 365 version of Excel 2016. It's not available to users of perpetual licensed versions of Excel 2016 or earlier versions of Excel. The function is an alternative to nesting multiple IF functions when displaying results based on a set of choices.

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.