How do I create a reusable custom function with LAMBDA in Excel?

Define the LAMBDA once in the Name Manager, giving it a name and the parameter list, then call that name like any built-in function. This lets you maintain the definition centrally and reuse it across worksheets.

Can LAMBDA return an array that spills into multiple cells?

Yes. If the calculation inside LAMBDA produces a multi-cell array, the calling cell will spill the result automatically, provided there is enough empty space. Make sure no other data blocks the spill range.

Why does my LAMBDA formula give #VALUE! even though the logic looks correct?

A common cause is a mismatch between the number of parameters you defined and the number you passed when invoking the function. Double-check that each required argument is supplied, and that optional arguments are either provided or omitted consistently.

Is it possible to nest LAMBDA inside other functions like FILTER or SORT?

Absolutely. LAMBDA can be used as an argument to functions that expect a lambda-style callback, such as MAP, BYROW, BYCOL, FILTER (via a helper LAMBDA), or even inside SORTBY to compute custom sort keys.

LAMBDA function

LAMBDA lets you create custom, reusable functions directly in a cell, returning any value or array based on supplied parameters.

=LAMBDA(parameter1, ..., calculation)

Generate a LAMBDA formula

Describe what you need. The generator will reach for LAMBDA where LAMBDA is the right tool, and tell you when it is not.

Arguments

How LAMBDA reads its arguments

Argument	Required	Description
parameter1	Required	The first required argument; can be any type (range, scalar, array) that the calculation will use; if omitted the function returns #VALUE!.
...	Repeating	Optional repeating parameters; each may be any data type and are passed to the calculation in order; excess arguments are ignored, missing ones become #N/A.
calculation	Required	A required expression that uses the supplied parameters to produce a result; must be a valid formula; if it evaluates to an error the LAMBDA returns that error.

Returns

It returns a scalar, array, or error value matching the shape produced by the calculation.

Availability

Excel: 365 · Google Sheets: Supported

Worked examples

1. Average list price of all listings

Address	Beds	Baths	List Price	Days on Market
123 Maple St	3	2	300000	45
456 Oak Ave	4	3	450000	30
789 Pine Rd	2	1	250000	60
321 Birch Blvd	5	4	600000	15

=LAMBDA(tbl, AVERAGE(INDEX(tbl,,4)))(A2:E6)

Result: 400000

The LAMBDA receives the whole table (A2:E6) as its single parameter tbl. Inside the calculation, INDEX(tbl,,4) extracts the fourth column – the list prices – and AVERAGE computes their mean. The four prices sum to 1,600,000, so the average is 400,000, which the formula returns.

2. Address of the property that has lingered longest on the market

Address	Beds	Baths	List Price	Days on Market
123 Maple St	3	2	300000	45
456 Oak Ave	4	3	450000	30
789 Pine Rd	2	1	250000	60
321 Birch Blvd	5	4	600000	15

=LAMBDA(tbl, INDEX(tbl, MATCH(MAX(INDEX(tbl,,5)), INDEX(tbl,,5),0),1))(A2:E6)

Result: 789 Pine Rd

The calculation first pulls the fifth column (days on market) with INDEX(tbl,,5) and finds its maximum (60). MATCH then locates the row where 60 appears. Finally INDEX returns the value from column one of that row – the address "789 Pine Rd" – which is the result.

3. Price per bed for each listing using MAP and LAMBDA

Address	Beds	Baths	List Price	Days on Market
123 Maple St	3	2	300000	45
456 Oak Ave	4	3	450000	30
789 Pine Rd	2	1	250000	60
321 Birch Blvd	5	4	600000	15

=MAP(A2:E6, LAMBDA(row, INDEX(row,4)/INDEX(row,2)))

Result: 100000112500125000120000

MAP iterates over each row of the table. The embedded LAMBDA receives a single row, extracts the list price (column 4) and the bed count (column 2), divides price by beds, and returns the per-bed price. The four calculations yield 100,000; 112,500; 125,000; and 120,000, returned as a vertical array.

Common errors

Which LAMBDA error are you seeing?

Error	Why it happens	How to fix it
#NAME?	LAMBDA is not recognized because the workbook is opened in a version of Excel that predates the 365 rollout or in Google Sheets that has the feature disabled.	Upgrade to Excel 365 (or later) or enable the LAMBDA preview in Google Sheets; then the function will be parsed correctly.
#VALUE!	The LAMBDA call supplies fewer arguments than the defined parameters, for example omitting the required calculation expression.	Provide all required arguments – at least one parameter and the calculation – or adjust the LAMBDA definition to match the number of arguments you intend to pass.
#REF!	Inside the LAMBDA the formula references a range that has been deleted, moved, or is otherwise invalid, such as A1000:E1000 in a sheet that only has 500 rows.	Correct the range reference to point to an existing range, or use dynamic functions like OFFSET or INDEX that adapt to the current size of the data.

Tips and when to use something else

Wrap complex logic in LAMBDA and then reuse it with LET to keep your workbook tidy.
When you need to apply a LAMBDA across many rows or columns, combine it with MAP, BYROW, or BYCOL for vectorised execution.
If you only need a one-off calculation without reusability, a plain formula with LET is often simpler than defining a separate LAMBDA.
For look-up scenarios, consider XLOOKUP or VLOOKUP instead of writing a custom search LAMBDA; they are faster and handle missing values automatically.

Related functions

LETLET lets you name a single intermediate result inside a formula, then return that result, making complex calculations clearer and easier to read.MAPMAP applies a custom LAMBDA to each element of a range, returning a new array of the same shape with the calculated results.XLOOKUPXLOOKUP returns the value from a specified return array that corresponds to a given lookup value, with optional handling for missing matches and search direction.IFThe IF function returns one value if a condition is true and another (or nothing) if it is false, letting you branch logic in a cell.INDEXINDEX returns the value or array of values from a specified position within a given range, based on row and optional column numbers.

Frequently asked questions

How do I create a reusable custom function with LAMBDA in Excel?: Define the LAMBDA once in the Name Manager, giving it a name and the parameter list, then call that name like any built-in function. This lets you maintain the definition centrally and reuse it across worksheets.
Can LAMBDA return an array that spills into multiple cells?: Yes. If the calculation inside LAMBDA produces a multi-cell array, the calling cell will spill the result automatically, provided there is enough empty space. Make sure no other data blocks the spill range.
Why does my LAMBDA formula give #VALUE! even though the logic looks correct?: A common cause is a mismatch between the number of parameters you defined and the number you passed when invoking the function. Double-check that each required argument is supplied, and that optional arguments are either provided or omitted consistently.
Is it possible to nest LAMBDA inside other functions like FILTER or SORT?: Absolutely. LAMBDA can be used as an argument to functions that expect a lambda-style callback, such as MAP, BYROW, BYCOL, FILTER (via a helper LAMBDA), or even inside SORTBY to compute custom sort keys.

Need a different formula?

The full generator is not scoped to one function — describe any spreadsheet problem and it will pick.

Open the formula generator

Reviewed 2026-09-17