This guide will explain how to use LAMBDA function in Google Sheets.
When we want to create a custom function and call it by a specific name, we can do this using the LAMBDA function in Google Sheets.
The rules for using the LAMBDA function in Google Sheets are the following:
- The
LAMBDAfunction is used to create and return a custom function with a set of names and a formula_expression that uses them. - The values we input in the name argument must be valid. For example, it must not be cell references such as A2 or B2.
- The name argument must not start with numbers.
- Special characters and spaces are not supported except for dots and underscore.
Google Sheets released new functions allowing us to create customized formulas and share them with other collaborators.
The LAMBDA function can help us create a customized formula with placeholders of values used in the formula. Furthermore, we can share these with others using the Named function.
We can use the LAMBDA function as a standalone or with other Lambda helper functions (LHFs), which can elevate the usage of the LAMBDA function. Some Lambda helper functions are the MAP function and REDUCE function.
In this guide, we will provide a step-by-step tutorial on how to use the LAMBDA function in Google Sheets. Additionally, we will explore the syntax and a real example of using the function.
Great! Let’s dive right in.
The Anatomy of the LAMBDA Function
The syntax or the way we write the LAMBDA function is as follows:
=LAMBDA([name],formula_expression)(function_call,...)
- = the equal sign is how we activate any function in Google Sheets.
- LAMBDA() is our
LAMBDAfunction. This function is used to create and return a custom function with a set of names and a formula that uses them. - name is an optional argument. This refers to the name to be used inside the formula_expression argument. This name must be an identifier and resolve to the actual value passed to the custom function returned by
LAMBDA. - formula_expression is a required argument. This refers to the formula we want to calculate or execute. We need to use the names we inputted in the name argument.
- function_call is used to input the actual values. When there is no name argument, we can use open and close brackets ().
Note: The syntax above differs slightly from the official documentation of the LAMBDA function in Google Sheets. The syntax above has been slightly modified to help us understand better.
A Real Example of Using LAMBDA Function in Google Sheets
Let’s say we have a data set consisting of the salaries received by an employee each month. Our initial data set would look like this:
The spreadsheet above shows the months, the salary received in each month, and the total salary. Let’s say we want to calculate the savings amount, assuming we want to save 40% of our total salary.
There is no built-in function for this calculation. Thus, we want to create a customized formula to reuse for other situations.
In this case, we will use the formula:
=LAMBDA(money,money*0.4)(B11)
The first part of the formula composes the name argument. We will use the name money as an identifier for the actual value.
Remember that we cannot use cell references, special characters, or names that start with a number. We can separate them by commas if there is more than one name.
Next, we will input our formula_expression argument. Since we want to calculate 40% of the total salary to keep as savings, the formula would be =salary*0.40.
We need to use the name we inputted in the first argument. Hence, our formula_expression would be =money*0.40. After this argument, we will close the function with a close bracket.
Then, we will input a cell reference containing the actual value in another bracket as our function_call argument. In this case, we are calculating 40% of the total salary, which is in cell B11.
Our final data set would look like this:
You can make your own copy of the spreadsheet above using the link below.
Amazing! Now we can dive into the steps of using the LAMBDA function in Google Sheets.
How to Use LAMBDA Function in Google Sheets
1. First, we will select an empty cell to display the result. Then, we will start our formula by typing an equal sign and the function name. Our starting formula would be “=LAMBDA(“.
2. We will input our identifier in the name argument. In this case, we will use the name money as our identifier. Then, our formula would become “=LAMBDA(money”.
3. Next, we will type in our formula to calculate 40% of the total salary. In this case, our formula would become “=LAMBDA(money,money*0.4)”.
4. Lastly, we will start another bracket and select the cell containing the total salary value. Our final formula would be “=LAMBDA(money,money*0.4)(B11)”.
5. We will press the Enter key to return the result.
And tada! We have successfully used the LAMBDA function in Google Sheets.
You can apply this guide whenever you need to create a customized formula and reuse it in different situations. You can now use the LAMBDA function and the various other Google Sheets formulas available to create great worksheets that work for you.
FAQs:
1. What are the common errors in using the LAMBDA function?
When there is a N/A error, it means the wrong number of arguments were used. For example, the formula does not contain the function_call argument, which calls the cells containing the actual values.
Another common error is the #NAME? error. If you encounter this error, an invalid identifier was used in the first argument, or a wrong function name was used in the formula_expression argument.
2. Do I have to use Lambda helper functions with the LAMBDA function?
Yes and No. In Google Sheets, we can use the LAMBDA function as a standalone to create a custom function and also use it with Lambda helper functions.
However, we must use the LAMBDA function with Lambda helper functions to create custom functions in Excel.
3. What are Lambda helper functions?
Lamba helper functions (LHFs) are native functions that accept a reusable LAMBDA as an argument. They allow us to perform advanced array operations and elevate our LAMBDA formula.
The LHFs available in Google Sheets are MAP, REDUCE, MAKEARRAY, BYROW, SCAN, and BYCOL function.
That’s pretty much it! We will be discussing in other guides how to use the Lamba helper functions. Make sure to subscribe to our newsletter to be the first to know about the latest guides and tutorials from us.
