Windows: 16.0.14729
Mac: 16.56 (Build 211211)
Web: introduced 18Mar2022
iOS: 2.56 (Build 211207)
Android: 16.0.14729
Use a LAMBDA function to create custom, reusable functions and call them by a friendly name. The new function is available throughout the workbook and called like native Excel functions.
You can create a function for a commonly used formula, eliminate the need to copy and paste this formula (which can be errorprone), and effectively add your own functions to the native Excel function library. Furthermore, a LAMBDA function doesn’t require VBA, macros or JavaScript, so nonprogrammers can also benefit from its use.
Syntax
=LAMBDA([parameter1, parameter2, …,] calculation)
Argument 
Description 

parameter 
A value that you want to pass to the function, such as a cell reference, string or number. You can enter up to 253 parameters. This argument is optional. 
calculation 
The formula you want to execute and return as the result of the function. It must be the last argument and it must return a result. This argument is required. 
Remarks

Lambda names and parameters follow the Excel syntax rules for names, with one exception: don't use a period (.) in a parameter name. For more information, see Names in formulas.

Make sure you follow best practices when creating a LAMBDA function as you do with any native Excel formula, such as passing of the correct number and type of arguments, matching open and close parentheses, and entering numbers as unformatted . Also, when you use the Evaluate command, Excel immediately returns the result of the LAMBDA function and you can't step into it. For more information, see Detect errors in formulas.
Errors

If you enter more than 253 parameters, Excel returns a #VALUE! error.

If an incorrect number of arguments is passed to a LAMBDA function, Excel returns a #VALUE! error.

If you call a LAMBDA function from within itself and the call is circular, Excel can return a #NUM! error if there are too many recursive calls.

If you create a LAMBDA function in a cell without also calling it from within the cell, Excel returns a #CALC! error.
Create a LAMBDA function
Here's a stepbystep process to follow that helps make sure your Lambda works as you intended and closely resembles the behavior of a native Excel function.
Make sure the formula you use in the calculation argument is working correctly. This is vital because as you create the LAMBDA function, you want to ensure that the formula works and you can rule that out if you encounter errors or unexpected behavior. For more information, see Overview of formulas in Excel and Create a simple formula in Excel.
A good practice is to create and test your LAMBDA function in a cell to make sure it works correctly, including the definition and the passing of parameters. To avoid the #CALC! error, add a call to the LAMBDA function to immediately return the result:
=LAMBDA function ([parameter1, parameter2, ...],calculation) (function call)
The following example returns a value of 2.
=LAMBDA(number, number + 1)(1)
Once you have finalized the LAMBDA function, move it to the Name Manager for final definition. By doing so, you give the LAMBDA function a meaningful name, provide a description, and make it reusable from any cell in the workbook. You can also manage the LAMBDA function as you can for any name, such as a string constant, a cell range, or a table.
Procedure

Do one of the following:

In Excel for Windows, select Formulas > Name Manager.

In Excel for Mac, select Formulas > Define Name.


Select New, and then enter information in the New Name dialog box:
Name:
Enter the name for the LAMBDA function.
Scope:
Workbook is the default. Individual sheets are also available.
Comment:
Optional, but highly recommended. Enter up to 255 characters. Briefly describe the purpose of the function and the correct number and type of arguments.
Displays in the Insert Function dialog box and as a tooltip (along with the Calculation argument) when you type a formula and use Formula Autocomplete (also called Intellisense).
Refers to:
Enter the LAMBDA function.
For example:

To create the LAMBDA function, select OK.

To close the Name Manager dialog box, select Close.
For more information, see Use the Name Manager.
Examples
Define the following in the Name Manager:
Name: 
ToCelsius 
Scope: 
Workbook 
Comment: 
Convert a Fahrenheit temperature to Celsius 
Refers to: 
=LAMBDA(temp, (5/9) * (Temp32)) 
Copy the example data in the following table and paste it in cell A1 of a new Excel worksheet. If you need to, you can adjust the column widths to see all the data.
Data 

104 

86 

68 

50 

32 

Formula 
Result 
=TOCELSIUS(A2) 
40 
=TOCELSIUS(A3) 
30 
=TOCELSIUS(A4) 
20 
=TOCELSIUS(A5) 
10 
=TOCELSIUS(A6) 
0 
Define the following in the Name Manager:
Name: 
Hypotenuse 
Scope: 
Workbook 
Comment: 
Returns the length of the hypotenuse of a right triangle 
Refers to: 
=LAMBDA(a, b, SQRT((a^2+b^2))) 
Copy the example data in the following table and paste it in cell A1 of a new Excel worksheet. If you need to, you can adjust the column widths to see all the data.
Data 

3 
4 
5 
12 
7 
24 
9 
40 
Formula 
Result 
=HYPOTENUSE(A2,B2) 
5 
=HYPOTENUSE(A3,B3) 
13 
=HYPOTENUSE(A4,B4) 
25 
=HYPOTENUSE(A5,B5) 
41 
Define the following in the Name Manager:
Name: 
CountWords 
Scope: 
Workbook 
Comment: 
Returns the word count in a text string 
Refers to: 
=LAMBDA(text, LEN(TRIM(text))  LEN(SUBSTITUTE(TRIM(text), " ", "")) + 1) 
Copy the example data in the following table and paste it in cell A1 of a new Excel worksheet. If you need to, you can adjust the column widths to see all the data.
Data 

Something wicked this way comes. 

I came, I saw, I conquered. 

A quick brown fox jumped over the lazy dog. 

Use the Force, Luke! 

Formula 
Result 
=COUNTWORDS(A2) 
5 
=COUNTWORDS(A3) 
6 
=COUNTWORDS(A4) 
9 
=COUNTWORDS(A5) 
4 
Define the following in the Name Manager:
Name: 
ThanksgivingDate 
Scope: 
Workbook 
Comment: 
Returns the date Thanksgiving in the USA falls on for a given year 
Refers to: 
=LAMBDA(year, TEXT(DATE(year, 11, CHOOSE(WEEKDAY(DATE(year, 11, 1)), 26, 25, 24, 23, 22, 28, 27)), "mm/dd/yyyy")) 
Copy the example data in the following table and paste it in cell A1 of a new Excel worksheet. If you need to, you can adjust the column widths to see all the data.
Data 

2020 

2021 

2022 

2023 

2024 

Formula 
Result 
=THANKSGIVINGDATE(A2) 
11/26/2020 
=THANKSGIVINGDATE(A3) 
11/25/2021 
=THANKSGIVINGDATE(A4) 
11/24/2022 
=THANKSGIVINGDATE(A5) 
11/23/2023 
=THANKSGIVINGDATE(A6) 
11/28/2024 
Need more help?
You can always ask an expert in the Excel Tech Community or get support in the Answers community.