It’s here! 🙂
The LAMBDA function in Google Sheets creates a custom function with placeholder inputs, that can be reused.
The use case for the LAMBDA function is to work with other lambda helper functions, like MAP, REDUCE, SCAN, MAKEARRAY, BYCOL, and BYROW.
LAMBDA functions are also the underlying technology for Named Functions, which are hugely useful.
LAMBDA functions themselves are challenging to use and not very intuitive compared to regular functions.
🔗 Get this example and others in the template at the bottom of this article.
Note: it may take up to 15 days from August 24th before you see lambda in your Sheets.
Simple LAMBDA Function Example
We’ll start with the simplest LAMBDA function we can: a custom function to double a number.
I.e. x => 2x
The LAMBDA formula to double a number is:
It works like so:
Of course, it’s much easier to simply type
=A1 * 2 so this lambda is just to illustrate how it works.
LAMBDA Function in Google Sheets: Syntax
The LAMBDA function takes one or more arguments:
This is optional and repeatable and represents the placeholders to use inside your formula.
The formula definition to calculate. It can reference any placeholder names that have been defined.
Notes on using the LAMBDA Function
The LAMBDA function is the only function that takes two sets of brackets for inputs, i.e. it takes the form
where the first set of brackets define LAMBDA and the second set are the actual input values you want to use.
If you try to use LAMBDA without adding actual values or cell references after the definition, you’ll see the following error:
LAMBDA Percent Change Example
Suppose we have a table of quarterly financial data.
We want to calculate the percent change between 2021 and 2022, using this formula:
Which gives the following output:
We can convert this to a LAMBDA formula by first defining the placeholders, then the function expression, and then supplying the actual values:
Has that helped us?
No, not really!
The LAMBDA formula is more complex than the original formula.
But as I said at the beginning of this article, the LAMBDA function is really designed to work with the new lambda helper functions.
And they also underpin Named Functions, so they’re critical in that regard.
So, instead of a LAMBDA function, we could create a named function PERCENTCHANGE and use it as follows:
Now that is easy!
LAMBDA Helper Functions
As I mentioned earlier in this post, the main use case for the LAMBDA function is to work with the lambda helper functions:
- MAP: Returns a new array formed by mapping each value in array_or_range to a new value
- REDUCE: Reduces an array_or_range by repeatedly applying a lambda to each value in array_or_range
- BYCOL: Returns a new row array formed by grouping each column in array_or_range to a single value
- BYROW: Returns a new column array formed by grouping each row in array_or_range to a single value
- SCAN: Scans array_or_range by repeatedly applying a lambda to each value in array_or_range
- MAKEARRAY: Returns a calculated array of specified rows and columns
Feel free to make a copy: File > Make a copy…
If you can’t access the template, it might be because of your organization’s Google Workspace settings.
In this case, right-click the link to open it in an Incognito window to view it.
Read more about lambda in the Google documentation.