Advanced Excel Formulas with LET and LAMBDA

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.