Excel LAMBDA Function

last modified October 5, 2026

The LAMBDA function lets you create custom, reusable functions in Excel using the formula language, without VBA, macros, or JavaScript. A LAMBDA is an anonymous formula that takes input values called parameters and returns the result of a calculation. Once you give it a name in the Name Manager, it behaves like a native Excel function and is available anywhere in the workbook. This tutorial provides a comprehensive guide to using LAMBDA with detailed examples. You'll learn the syntax, immediate invocation, named custom functions, recursion, and how to combine LAMBDA with array helpers such as MAP, BYROW, REDUCE, and SCAN.

LAMBDA Function Basics

The LAMBDA function separates a calculation from the cells it works on. Instead of writing =B5*C5, which is tied to specific cells, you write =LAMBDA(x, y, x*y) and supply the values later. The result is a definition that is ready to run but has no data yet. Values reach the LAMBDA in one of three ways: you provide them in a second set of parentheses, you call the LAMBDA by a name defined in the Name Manager, or you hand the LAMBDA to a helper function such as MAP that supplies values automatically.

Component	Description
Function Name	LAMBDA
Syntax	=LAMBDA([parameter1, parameter2, ...], calculation)
Arguments	0-253 parameters + calculation
Return Value	Result of the calculation
Availability	Excel for Microsoft 365, Excel 2024, and Excel for the web

This table breaks down the essential components of the LAMBDA function. It shows the function name, basic syntax format, argument limits, return value characteristics, and the versions that support it.

Argument	Meaning
parameter1, parameter2, ...	Optional input values that the function accepts, such as a cell reference, string, or number. A LAMBDA can have up to 253 parameters. The names follow the usual Excel naming rules, except that a parameter name cannot contain a period.
calculation	The formula that the function executes and returns. It must be the last argument and it is required. It must produce a single result.

The parameters are optional, so a LAMBDA may take zero arguments, but the calculation is mandatory and always comes last. If a LAMBDA is called with the wrong number of arguments, Excel returns a #VALUE! error. Supplying more than 253 parameters also produces #VALUE!. Entering a LAMBDA in a cell without calling it returns a #CALC! error, because there is nothing yet to display.

Basic LAMBDA Example

This example demonstrates the simplest use of LAMBDA to create a custom function that doubles a number.

=LAMBDA(x, x*2)(5)

This formula defines a LAMBDA that takes one parameter (x) and returns x*2. We immediately call it with the value 5, resulting in 10. This shows the basic structure of defining and invoking a LAMBDA. Without the trailing (5), the formula would return #CALC! instead of a number.

Immediate Invocation

A LAMBDA can be called on the spot by adding an argument list in a second set of parentheses. The values are matched to the parameters by position. This is the fastest way to test a custom function before naming it.

=LAMBDA(x, x*2)(5)          ' returns 10
=LAMBDA(x, y, x + y)(3, 4)  ' returns 7
=LAMBDA(x, x^2)(5)          ' returns 25

In the second formula, 3 is passed to x and 4 to y, so the result is 7. The argument list must follow the closing parenthesis of the LAMBDA with no space; adding a space can make Excel reject the formula or return #VALUE!.

A LAMBDA with no parameters is invoked with an empty pair of parentheses. For example, a zero-parameter LAMBDA defined as =LAMBDA(TODAY()) and named MYDATE is called with =MYDATE(), and the immediate form =LAMBDA(TODAY())() returns the current date. The empty parentheses are required, because they are what triggers the calculation.

Creating Named LAMBDA Functions

To make a LAMBDA reusable, you name it in Excel's Name Manager. This turns the anonymous formula into a custom function that can be called from any cell in the workbook. The table shows the steps.

Step	Action
1	Go to Formulas > Name Manager
2	Click New
3	Enter name (e.g., DoubleIt)
4	In Refers to: enter =LAMBDA(x, x*2)
5	Click OK

The table shows the steps to create a named LAMBDA function called DoubleIt. After defining it, you can use =DoubleIt(5) anywhere in your workbook to get 10. The testing values are left out of the Name Manager entry; the name itself becomes the function.

=DoubleIt(5)    ' returns 10
=DoubleIt(A1)   ' doubles the value in A1

The first formula calls the named LAMBDA directly with 5 and returns 10. The second applies it to the value in cell A1. If A1 contains 8, the result is 16. Named LAMBDAs make your formulas more readable and maintainable, and editing the definition once in the Name Manager updates every formula that uses it.

LAMBDA with Multiple Parameters

LAMBDA can accept multiple parameters, enabling more complex calculations. This example calculates the area of a rectangle.

A	B	C
Length	Width
10	5
		=LAMBDA(l,w, l*w)(A2,B2)

This table demonstrates a LAMBDA with two parameters (length and width) that calculates area. The formula in C2 multiplies the values from A2 and B2.

=LAMBDA(length, width, length*width)(A2,B2)

This formula defines and immediately calls a LAMBDA that calculates rectangle area. With A2=10 and B2=5, the result is 50. Descriptive parameter names improve readability, and the order of the parameters determines which argument supplies which value.

LAMBDA Parameters and Argument Rules

Parameters are the named placeholders inside a LAMBDA. They behave like the variables declared by the LET function. The following rules apply when you define them.

Rule	Detail
Maximum count	A LAMBDA accepts up to 253 parameters.
Order	Arguments are matched to parameters by position, so the first value goes to the first parameter and so on.
Naming	Parameter names follow Excel name rules, but a period is not allowed. A name that looks like a cell reference, such as x1, is invalid.
Optional arguments	Enclose a parameter in square brackets and test it with ISOMITTED to make the argument optional.
Wrong argument count	Calling the function with too few or too many values returns #VALUE!.

A custom function belongs to the workbook in which it is defined. A formula that calls a name that is not present in the workbook, or that is opened in a version of Excel without LAMBDA, returns a #NAME? error.

Recursive LAMBDA for Factorial

A named LAMBDA can call itself. This is called recursion, and it lets a function repeat a calculation an unknown number of times. A recursive function needs a test that decides when to stop and a call to itself with a smaller version of the problem. This example calculates a factorial.

Name:      FACT
Refers to: =LAMBDA(n, IF(n<=1, 1, n*FACT(n-1)))

=FACT(5)    ' returns 120
=FACT(4)    ' returns 24

The definition is stored in the Name Manager under the name FACT, and its calculation refers to that same name. The IF function provides the base case: when n is 1 or less, the function returns 1 and the recursion stops. Otherwise it multiplies n by FACT(n-1), so =FACT(5) evaluates as 5*4*3*2*1 = 120.

An anonymous LAMBDA cannot recurse, because it has no name to call. Recursion therefore requires a named LAMBDA defined in the Name Manager. A recursive formula that never reaches its base case calls itself until Excel returns a #NUM! error. Use IF for the stopping test, since IFS and SWITCH evaluate all of their arguments, including the recursive call, and would never terminate.

LAMBDA with MAP

LAMBDA works well with Excel's dynamic arrays. The MAP function applies a LAMBDA to each element of one or more arrays and returns an array of the same shape. This example squares each number in a range.

A	B
1
2
3
	=MAP(A1:A3, LAMBDA(x, x^2))

The table shows how to square each number in range A1:A3 using MAP and LAMBDA. The result in B1:B3 would be 1, 4, and 9 respectively.

=MAP(A1:A3, LAMBDA(x, x^2))

This formula applies the squaring operation to each cell in A1:A3. The result is a dynamic array of squared values. Combining LAMBDA with array functions enables powerful data transformations.

LAMBDA with BYROW

BYROW applies a LAMBDA to each row of an array and returns one result per row. Unlike MAP, which passes single values, the LAMBDA receives the whole row.

A	B
1	2
3	4
5	6

=BYROW(A1:B3, LAMBDA(row, SUM(row)))

The LAMBDA runs once for each row, with row holding both values in that row. The sums are 1+2=3, 3+4=7, and 5+6=11, so the formula spills the results 3, 7, and 11 into three cells. Use BYCOL for the equivalent operation down each column.

LAMBDA with REDUCE

REDUCE folds an array into a single value. It takes an initial value, an array, and a LAMBDA with two parameters: the accumulator and the current element. Each call returns a new accumulator.

A
1
2
3

=REDUCE(0, A1:A3, LAMBDA(acc, v, acc + v))

Starting from 0, the accumulator grows as 0+1=1, 1+2=3, and 3+3=6. The final result is 6. REDUCE is often used in place of recursion for problems that accumulate a result over a list.

LAMBDA with SCAN

SCAN is closely related to REDUCE, but instead of returning only the final value it returns every intermediate value. This makes it the natural tool for running totals.

A
1
2
3

=SCAN(0, A1:A3, LAMBDA(acc, v, acc + v))

The accumulator passes through 1, then 3, then 6, so the formula spills 1, 3, and 6 into three cells. That is the running total after each element, while REDUCE would return only the last value, 6.

Error Handling in LAMBDA

You can include error handling directly in your LAMBDA functions. This example shows how to handle division by zero.

=LAMBDA(a,b, IF(b=0, "Error", a/b))(5,0)

This formula attempts to divide 5 by 0 but returns "Error" instead of #DIV/0!. Built-in error handling makes your LAMBDA functions more robust and user-friendly. You can also wrap the result in IFERROR when the possible errors are varied.

LAMBDA Errors and Troubleshooting

Cause	Symptom	Fix
LAMBDA entered in a cell but never called	#CALC! error	Add an argument list in a second set of parentheses, or define the LAMBDA as a named formula
Wrong number of arguments passed	#VALUE! error	Match the call to the parameter count; required parameters cannot be omitted
More than 253 parameters	#VALUE! error	Reduce the parameter count or pass a range instead of many values
Recursive call never reaches its base case	#NUM! error	Add a stopping test with IF so the recursion terminates
Calculation argument is missing	Formula is rejected	The calculation must be the last argument and is required
Parameter name contains a period or looks like a cell reference	Name is rejected	Use a simple name such as value or row
Named LAMBDA is not defined in the workbook	#NAME? error	Check the spelling and scope in the Name Manager, or open the workbook in a version that supports LAMBDA

The LAMBDA function revolutionizes Excel by enabling custom function creation without VBA. From simple calculations to recursive algorithms and array transformations, LAMBDA handles it all elegantly. It is available in Excel for Microsoft 365, Excel 2024, and Excel for the web, but not in Excel 2021 or earlier. Remember that an anonymous LAMBDA must be invoked to produce a result, that recursion requires a named LAMBDA, and that the calculation is always the required last argument. Mastering LAMBDA will significantly enhance your Excel capabilities and make your workbooks easier to read and maintain.

Author

My name is Jan Bodnar, and I am a passionate programmer with extensive programming experience. I have been writing programming articles since 2007. To date, I have authored over 1,400 articles and 8 e-books. I possess more than ten years of experience in teaching programming.

List all Excel Formulas.