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.
1 Day
€375.00
 

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
ExcelMicrosoft ExcelOffice 365Excel 365LAMBDA