Create Named Functions in Google Sheets

While the primary purpose of the LAMBDA Library is to create functions for use in Excel, most of the pages in this library also include functions written for Google Sheets.

Before continuing, make sure that you have good understanding of the Named Function feature in Google Sheets, by reading this article:

Must Read First: Create & Use Named Functions

Warning: Excel vs. Google Sheets Compatibility

The Named Functions in Google Sheets and the Named LAMBDA functions in Excel are not currently compatible. There are many reasons and also many individual function nuances that make them incompatible.

This might be resolved some day, but for now realize that if you are using named functions, you aren't going to be able to transition back and forth between editing your file with Excel, the Google Sheets, then Excel, etc.

How to Get the LAMBDA Library Functions into Google Sheets

Method 1: Copy a Template with Functions Already Included

This is by far the simplest and fastest approach. Our template includes working examples of the functions, and after making your own copy of the file, you can import specific functions from the template into other sheets.

Method 2: Manually, One at a Time

On each of the function pages, the function code can be found in a tab like the one below:

Name: AREA_RECTANGLE
Description: Calculate the area of a rectangle
Arguments: length, width
Function:

LET(doc,"https://www.vertex42.com/lambda/named-functions-in-sheets.html",
    length * width
)

Step 1: In Google Sheets, Go to Data > Named Functions

Step 2: Enter the function Name and Description

You can get this information from the code box above, but you could also make up your own name and description.

Step 3: Enter the arguments, one at a time.

In this example, enter length, then width.

Step 4: Copy/Paste the formula.

Copy/paste the code for the LET function from the above code box, including the closing ")". The functions will not normally be as simple as "length * width" but this is just an example.

The functions within the LAMBDA library will always use the LET function, because the "doc" paramater is used to list the hyperlink of the documentation page. Also, more complicated formulas tend to be easier to understand when using LET.

Step 5: (Optional) Define parameter descriptions and examples

This is not a necessary step, but if you want to provide an explanation for the parameter values and example inputs, you can do that in this step.

Step 5: Test the new function.

Set up a simple example to test the new function. Notice that the information you enter for descriptions is contained within the tooltip help for the function as you are entering it into a cell.

What About the LAMBDA?

Unlike Excel, the LAMBDA function is not needed when you create a Named Function in Google Sheets. It is likely already built into their Named Function feature. Their UI for creating the Named Function is basically helping you set up a named LAMBDA, though you don't actually see "LAMBDA" anywhere.

The LAMBDA function DOES exist in Google Sheets, and is needed when using REDUCE, SCAN, BYROW, BYCOL, MAP, and MAKEARRAY. You can also define a function using =LET(myfun,LAMBDA(...)) which you may see in more complicated formulas.