LAMBDA - Using the Ultimate Excel Function
Course Description
Office 365 subscribers have access to LAMBDA, which Microsoft calls "The Ultimate Worksheet Function". Using LAMBDA, you can create your own user defined functions without needing to work with VBA. LAMBDA allows you to greatly simplify complex formulas by breaking them into small and simple user defined functions (Lambdas). Simplifying complex formulas will make your spreadsheets more understandable, maintainable, secure and with less potential for errors. Lambdas and the associated Lambda Helper Functions along with Dynamic Arrays and the LET function will radically improve the way complex spreadsheets are written. Understanding these innovations is vital for any Excel Power User. AI Assistants like ChatGPT and Copilot are aware of these functions and can be utilised to create user-defined functions using LAMBDA and simplify existing formulas using LET.
Duration: 1 days
Prerequisites
Participants on this course should be comfortable with Excel and creating sheets with complex formulas. Attendance on our Excel Intermediate and Excel Advanced courses or having equivalent knowledge is recommended.
Introduction
Why is the LAMBDA function called - The Ultimate Excel function - by Microsoft What is a Lambda? Why not use VBA? Pros of Lambdas Cons of Lambdas Lambda limitations
A First Lambda
The LAMBDA() function Creating a Lambda in a cell Passing parameters to your Lambda Naming your Lambda Calling your Lambda
Recap of Intermediate and Advanced Excel Features
Working with Lambdas means understanding some high intermediate or advanced Excel functions and formula techniques. This section is a short recap on the key ones. Boolean functions AND and OR IFS VLOOKUP and XLOOKUP INDEX and MATCH Understanding Arrays And Array Functions Functions that return arrays SPILL Error Using the LET function to break a complex formula into individual steps The Advanced Formula Environment
Creating A Lambda
Creating a formula Create a Lambda using the Advanced Formula Environment Identify the steps needed to make this formula a Lambda Decide parameters for the Lambda Decide return from Lambda Create the Lambda and use it in a cell Build Lambda one step at a time and verify each step is working Testing your Lambda
Intermediate Lambda Topics
Passing a Lambda to a Lambda Optional parameters - ISOMITTED function Working with a range as a parameter to a Lambda Using SEQUENCE() and INDEX() to operate on a range
Lambda Helper Functions
MAKEARRAY BYROW and BYCOL MAP SCAN
AI Assistants
Overview of AI Assistants Get AI Assistant to simplify a complex formula using LET Ask AI Assistant To Explain A Complex Formula Containing LET Get AI Assistant to create a UDF using LAMBDA
Lambda Development
Importing and sharing Lambdas Testing techniques Debugging tips and tricks Recursive Lambdas
More Practical Examples
Using Lambdas to simplify complex formulas Simplify calculations for using mileage rate bands Simplify complex lookups using Lambdas to compare two lists Sort a range using a complex id that is made up of letters and numbers
Schedule
| Name | Date | Location | |
|---|---|---|---|
| LAMBDA - Using the Ultimate Excel Function | 2024-11-12 | Online |
Excel Microsoft Excel Office 365 Excel 365 LAMBDA