Excel Error Codes

How to Fix the #REF Error When Calling an Excel LAMBDA Function

Phi Hung Vo Sep 29, 2026 868 views

Question details

The user needs to resolve a #REF error that occurs when attempting to call a LAMBDA function directly from a cell reference instead of an officially registered name.

How to Fix the #REF Error When Calling an Excel LAMBDA Function

Product: Microsoft Excel
Device & OS: not provided
Scenario: Creating and executing custom LAMBDA functions within a spreadsheet to automate repetitive calculations.
Observed behavior: Referencing a cell containing a LAMBDA formula retrieves its static result rather than executing the function, which triggers a #REF error.

Before you start

Verify that your base LAMBDA formula syntax is correct and test it directly in a cell by appending dummy variables to the end of the formula before proceeding.

Solution 1Recommended

Register the LAMBDA Function via Name Manager

To successfully call a LAMBDA function across your workbook without triggering a #REF error, you must globally define it as a named formula.

Excel does not automatically register LAMBDA formulas typed directly into a cell as callable functions. Referencing the cell only pulls the cell's static value. To make the function reusable, you must explicitly define it within the Name Manager.

Access the Name Manager

Navigate to the 'Formulas' tab on the top ribbon menu and click on 'Name Manager'.

Create a New Named Formula

Click the 'New' button to open the New Name dialog box.

Define the Function Name

In the 'Name' field, type a descriptive, space-free name for your custom function (for example, MyLambda).

Input the LAMBDA Logic

In the 'Refers to' field at the bottom, enter your complete LAMBDA formula, exactly as you would write it, such as =LAMBDA(a,a).

Call the Registered Function

Return to your worksheet. You can now execute your custom function in any cell by typing its assigned name with the required parameters, like =MyLambda(1).

Register the LAMBDA Function via Name Manager

Testing Function Logic: You can test your LAMBDA logic directly in a worksheet cell before adding it to the Name Manager by passing parameters immediately after the formula: =LAMBDA(x,y, x+y)(5,10).

Advanced Spreadsheet Capabilities

Manage Custom Formulas Easily with WPS Office

WPS Spreadsheets provides a highly intuitive Name Manager, enabling you to define, organize, and execute advanced custom formulas quickly, ensuring your data analysis workflows remain accurate and error-free.

1. Download WPS Office: Download and install WPS Office for free from the official website.
2. Open Your Spreadsheet: Launch WPS Spreadsheets and open your existing workbook containing complex formulas.
3. Navigate to Formulas: Go to the Formulas tab on the main ribbon and select Name Manager.
4. Define New Functions: Click New to define your custom formula logic and assign it a globally recognized name.

Fully compatible with Microsoft Excel .xlsx formats and standard workbook formulasUser-friendly Name Manager for seamless custom function deployment and trackingFree and lightweight alternative to heavy office suitesCross-platform support ensuring your formulas work on Windows, Mac, and Linux

microsoft office alternative - wps office

Frequently Asked Questions

Can I test a LAMBDA function without using the Name Manager?

Yes, you can test a LAMBDA function directly within a worksheet cell by appending the required parameters inside parentheses at the very end of the formula, such as =LAMBDA(x, x*2)(5). This returns the calculated result immediately.

Why do I get a #REF error when dragging a LAMBDA formula across cells?

If your LAMBDA formula uses relative cell references inside its logic instead of defined parameters, dragging it can cause those references to shift out of the worksheet's bounds, resulting in a #REF error. Always pass variables as parameters within the LAMBDA structure.

Are named LAMBDA functions available across the entire workbook?

By default, when you define a new function in the Name Manager, its scope is set to 'Workbook'. This means you can call your custom LAMBDA function from any worksheet within that specific file.

Microsoft Office Error Codes