Excel LAMBDA #VALUE! Error: Causes and Fixes

An Excel LAMBDA #VALUE! error usually means the LAMBDA function was called with the wrong number of arguments, or that its calculation produced a value error. Compare the function’s parameters with the arguments in the call, then check the input values and any functions used inside the calculation.

What the LAMBDA #VALUE! error means

LAMBDA lets you create a reusable, custom function in a workbook. Its definition lists parameters, which act as placeholders, followed by a calculation that uses those placeholders. When you call the LAMBDA, you supply an argument for each required parameter. If the call supplies too few or too many arguments, Excel can return #VALUE! because the call does not match the function’s definition.

The same error can also come from the calculation itself. For example, a LAMBDA that multiplies its input by two will return #VALUE! if the input is text that Excel cannot use in that arithmetic operation. The error therefore does not always mean the LAMBDA syntax is wrong; it may be an input or an error passed through from a nested function.

LAMBDA is available in Microsoft 365 and Excel 2024. Excel also displays other errors that are easy to confuse with #VALUE!. A LAMBDA entered in a cell without being called returns #CALC!, while a misspelled or unavailable function name can produce #NAME?. Identifying the exact error is a useful first step.

How LAMBDA parameters and arguments work

A LAMBDA definition has this structure: parameters come first, then a comma, then the calculation. Each parameter is a name for a value the function will receive when called. The number of parameters tells you how many arguments the call must provide.

With no worksheet inputs because the value is supplied directly in the call, this example defines a function that doubles one input; entered by itself, it returns #CALC! because the LAMBDA has not been called: =LAMBDA(inputValue,inputValue*2)

With no worksheet inputs because the value is supplied directly in the call, this example immediately calls that one-parameter function with 5 and returns 10: =LAMBDA(inputValue,inputValue*2)(5)

With no worksheet inputs because the value is supplied directly in the call, this call provides two arguments to a function with only one parameter and returns #VALUE!: =LAMBDA(inputValue,inputValue*2)(5,10)

For a function with two parameters, both must be supplied in the call. Argument order matters too: the first supplied value goes to the first parameter, the second to the second. A function can accept a number of different inputs, but it does not infer a missing value just because the calculation could be completed without it.

Define and call a named LAMBDA

A named LAMBDA is easier to reuse and troubleshoot than a long function written repeatedly in cells. In Excel for Microsoft 365 on Windows, open Formulas > Name Manager, select New, enter a name such as TwiceValue, and put the LAMBDA definition in Refers to. Use a name that is not already used in the workbook, and select workbook scope if the function should be available throughout the workbook.

With no worksheet inputs because this is the definition stored in Name Manager, this LAMBDA creates the one-argument TwiceValue function: =LAMBDA(inputValue,inputValue*2)

After creating that name, a worksheet formula can call it. With the value 5 supplied directly and no cell references, this call returns 10: =TwiceValue(5)

With the named function defined above and no cell references, this call supplies two arguments although TwiceValue has one parameter, so it returns #VALUE!: =TwiceValue(5,10)

Check the Refers to entry if a named call fails. The definition might have a different parameter count from what you remember, or the name could have been created with worksheet-level rather than workbook-level scope. A name defined only for one sheet may not be recognized on another. In Excel for Mac, the formula logic is the same; the ribbon layout can differ, so use the Formulas tab and its Name Manager command if available in your version.

Common causes and how to recognize them

Too few or too many arguments: Count the parameters in the LAMBDA definition, then count the arguments between the parentheses in the call. A one-parameter function needs one supplied argument.
An unexpected input type: A value that looks like a number may be stored as text, or a cell may contain a label such as Not available. If the calculation performs arithmetic on that value, it may return #VALUE!.
An error inside the calculation: A LAMBDA returns errors from calculations and functions it uses. For instance, a faulty text conversion, calculation, or lookup inside the LAMBDA can be the real source, even when the outer call has the correct number of arguments.
A nested function has its own argument issue: The outer LAMBDA call can be correct while a function inside its calculation receives an unsuitable value or arguments. Inspect each nested function rather than changing the LAMBDA call at random.
A mistaken assumption about optional inputs: LAMBDA parameters are not automatically optional. If the function is designed with two parameters, omitting the second is not the same as passing a blank value; the call needs to match the definition.
Changes to a named definition: If a named LAMBDA was edited, existing calls may no longer match its parameter list. Review Name Manager and update the calls or restore the intended definition.

Argument separators depend on Excel’s regional settings. The examples here use commas, as in standard English-language Excel formulas. Some installations use semicolons in formulas; if Excel rejects an otherwise correct formula while entering it, check the list separator used by your installation. That is a formula-entry issue and is distinct from a #VALUE! result returned after a formula is accepted.

Steps to troubleshoot the error

Confirm the exact error. Select the cell and verify that it displays #VALUE!, not #CALC!, #NAME?, or another error. Each points to a different likely problem.
Count parameters and arguments. If the LAMBDA has three parameters, check that its call supplies three arguments in the intended order.
Inspect a named definition. Open Formulas > Name Manager, select the relevant name, and review Refers to. Confirm the definition and the name’s scope.
Test with simple known values. Temporarily call the function with direct values that match its expected input types. If the test works, investigate the values in the worksheet cells used by the failing call.
Check the calculation in pieces. Look at the expressions inside the LAMBDA and test the relevant part separately. Excel’s Formulas > Evaluate Formula command can step through a formula on Windows and help reveal where the error appears.
Trace input cells. Check whether referenced cells contain text, blanks, errors, or unexpected values. A cell that appears empty may also contain a formula returning an empty string, which is text rather than a number.
Retest the original call. Once the definition or input is corrected, recalculate the formula and confirm that the result is sensible for the data, not merely free of an error.

A concrete example with worksheet cells

Suppose A2 contains the number 12. The goal is to use a named LAMBDA called TwiceValue to multiply the value by two. The definition in Name Manager has one parameter, inputValue, and the calculation inputValue*2. With A2 as the sole input, the worksheet call is:

With the named function defined as above and the value 12 in A2, this formula returns 24: =TwiceValue(A2)

If the call is accidentally entered with an extra argument, it no longer matches the definition:

With A2 containing 12 and TwiceValue still having one parameter, this formula has an extra argument and returns #VALUE!: =TwiceValue(A2,2)

Now suppose A2 contains the text word twelve rather than the numeric value 12. The argument count is correct, but the multiplication cannot use that text as a number in the ordinary way, so the calculation can return #VALUE!. Correct the source data if it is meant to be numeric, or deliberately convert and validate text in the function design. Do not assume that changing the argument count will fix an input-type problem.

This distinction is important when a formula works for most rows but fails for one. The function definition may be fine; one row can contain a text number, a stray space, or a different kind of value. Compare a failing row with a working row and inspect the underlying cell contents.

How #VALUE! differs from other LAMBDA errors

#CALC! is the characteristic result when you enter a LAMBDA definition in a cell without invoking it. Excel has a function definition but no supplied argument to calculate with. Call it directly or save it as a named function and call that name.

#NAME? often indicates that Excel does not recognize the function or name. Check the spelling of the named LAMBDA, whether it exists in the workbook, and whether the Excel version supports LAMBDA. LAMBDA is available in Microsoft 365 and Excel 2024; an older Excel release may not recognize it.

#N/A may come from a lookup or another calculation inside the LAMBDA when it cannot find a requested value. #NUM! and other errors can also originate inside the calculation. The outer LAMBDA does not automatically replace or explain errors produced by its contents, so follow the error to the inner operation that creates it.

Ways to prevent repeat errors

Keep the function’s purpose and expected inputs clear. Use descriptive parameter names, such as inputValue or taxRate, rather than names that are hard to distinguish. Avoid parameter names that resemble cell references, and make the number and order of parameters apparent in the function name or documentation.

Test a new LAMBDA with ordinary values, boundary cases, and the kinds of imperfect data the workbook may contain. For example, test a valid number, a blank cell, and text if those values could occur in the source data. Decide whether the function should reject such input, convert it, or return a deliberate message. A function should handle these cases intentionally rather than silently disguise them.

When modifying a named function, review existing calls before changing its parameter list. A function used across many sheets can have many callers, and a definition change may break them even if the new version works in a single test cell. Keep a small set of test formulas in a development area or copy the workbook before substantial changes.

Frequently Asked Questions

Why does a LAMBDA formula return #VALUE! when I enter the right function name?
Check the argument count and order first. Then verify that each input has a type the calculation can use and inspect nested functions for their own errors.

Does entering a LAMBDA by itself cause #VALUE!?
Usually not. A LAMBDA definition entered without a call returns #CALC!, because it has not been invoked. Call it with its arguments or store it as a name and call that name.

Can a blank argument cause a LAMBDA #VALUE! error?
It can, depending on the calculation. A blank cell may behave differently in arithmetic than text or a number, and a formula returning an empty string is text. Check how the LAMBDA uses that input.

Why does my named LAMBDA work on one sheet but not another?
Check the name’s scope in Name Manager. A name created with sheet-level scope is available only on that sheet; a workbook-scoped name can be used throughout the workbook.

Will LAMBDA work in older versions of Excel?
LAMBDA is available in Microsoft 365 and Excel 2024. If a workbook is opened in a release that does not support it, Excel may show an unrecognized-name error rather than calculate the function.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Errors & fixes

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.