How to Use the LAMBDA Function in Google Sheets to Create Custom Formulas

The Problem with Repetitive Formulas

If you regularly work with Google Sheets, you likely have a few complex formulas that you use constantly. Perhaps you have a massive nested IF statement that calculates employee bonuses based on tiers, or a long REGEXEXTRACT formula used to pull specific data from messy text strings. Typing or copying these formulas repeatedly is not just tedious; it drastically increases the chance of a typing error that breaks your entire spreadsheet.

Google Sheets introduced the LAMBDA function to solve this exact problem. The LAMBDA function allows you to take any complex formula you have written, give it a custom name (like CALCULATE_BONUS), and then use that name as if it were a native, built-in function.

How the LAMBDA Function Works

Unlike standard functions like SUM() or AVERAGE(), LAMBDA acts as a wrapper. It requires two main components:

1. The Parameters: The inputs your custom formula will need (e.g., “Sales_Amount”, “Years_Employed”).
2. The Formula: The actual calculation to perform using those parameters.

The basic syntax looks like this:

=LAMBDA(name1, name2, formula_expression)

Step 1: Writing and Testing Your Formula

Before you convert a formula into a LAMBDA function, you must write it normally to ensure it works.

Imagine you have a sales figure in cell A2 (e.g., $5,000). You want a formula that calculates a 10% commission only if the sales figure is over $1,000. If it is under $1,000, the commission is $0.

Your standard formula would be:

=IF(A2>1000, A2*0.10, 0)

Test this in your sheet to ensure it outputs the correct number.

Step 2: Converting to LAMBDA

Now, wrap that formula inside the LAMBDA function. Instead of using the specific cell reference (A2), you will invent a generic parameter name, such as revenue.

=LAMBDA(revenue, IF(revenue>1000, revenue*0.10, 0))

If you type this directly into a cell and press enter, Google Sheets will return a #CALC! error. This is expected. A LAMBDA function by itself does nothing unless you feed it data. To test it immediately in the cell, you must add the data in parentheses at the very end:

=LAMBDA(revenue, IF(revenue>1000, revenue*0.10, 0))(A2)

If this outputs the correct calculation, your LAMBDA formula is ready to be saved.

Step 3: Saving Your Custom Function (Named Functions)

The true power of LAMBDA is saving it so you never have to look at the ugly code again.

1. Copy the core LAMBDA formula to your clipboard: =LAMBDA(revenue, IF(revenue>1000, revenue*0.10, 0))
2. In the Google Sheets top menu, click on Data.
3. Select Named functions from the drop-down menu.
4. A sidebar will open on the right. Click the Add new function button.
5. Function Name: Give your new function a descriptive name. It must be all caps with no spaces (e.g., CALCULATE_COMMISSION).
6. Function Description: Write a brief note on what it does (e.g., “Calculates a 10% commission for sales over $1k”).
7. Formula Definition: Paste your copied LAMBDA formula into this box.
8. Click Next, and then click Create.

Using Your New Custom Function

Your custom function is now part of your spreadsheet! You can use it exactly like a native Google Sheets function.

Simply click on any empty cell, type =CALCULATE_COMMISSION(, click on the cell containing your sales data, and close the bracket. Google Sheets will instantly run your complex underlying code and output the result, keeping your spreadsheet incredibly clean and easy to read.