Skip to main content
 

Excel LAMBDA Function (365)

Author: Sun Last Modified: 2024-10-09
showing the usage of LAMBDA function

Description

The Excel LAMBDA function is used to create custom functions that can be reused throughout a workbook. And once a LAMBDA function is created and tested, it can be defined as a friendly name for calling.

Formula syntax

LAMBDA([parameter1, parameter2, …],calculation)

Arguments

  • paremeter1,parameter2,...: Optional, the value that you want to pass to the function, they can be cell reference, ,strings, numbers, Up to 253 parameters are allowed to entered.
  • calculation: Required, the calculation to perform as the result of the function. It must be the last argument and it must return a result.

Remarks

LAMBDA names and parameters follow the Excel syntax rules for names, except that, do not use period (.) in a parameter name.

In computer programming, the term LAMBDA refers to an anonymous function or expression, which means that once a generic version of LAMBDA function has been created and tested, you can port it to Name Manager, and formally define this formula as a name.

There just needs one copy of code to update when fixing problems or updating functionality, and changes will automatically propagate to all instances of the LAMBDA function in a workbook because the logic contained in the formula that LAMBDA function created exists in just one place.

Errors

1) #Value! Error appears when:
More than 253 parameters are entered in the formula;
An incorrect number of arguments is passed to LAMBDA function.

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

3) #CALC! error appears when you create a LAMBDA function in a cell without also calling it from within the cell.

Version

Excel 365

How to use LAMBDA to create a custom formula

Take an instance, to get the result of x*y+1, x in column A2:A7, y in column B2:B7, please follow the below steps to create the custom formula by using LAMBDA function and name it.

1. Test the formula

Firstly, you need to test that the arguments you will use in the calculation are working correctly.

In a cell, type the standard formula

=A2*B2+1

Press Enter key to test if the arguments in the calculation are correct.
showing the usage of LAMBDA function

2. Create the LAMBDA formula

To avoid the #CALC! error, please add a call to the LAMBDA function to get the result correctly.

In a cell, type the LAMBDA function as this:

=LAMBDA(x,y,x*y+1)(A2,B2)

Press Enter key.

Here A2 = x, B2=y.
showing the usage of LAMBDA function

3. Define a name for the LAMBDA formula

After the LAMBDA formula is created and tested, add it to the Name Manager and define a name for recalling next time in the workbook.

Click Formulas > Define Name, in the popping New Name dialog, define a name in Name textbox for the new formula, and type the LAMBDA formula into Refers to textbox. Click OK.
showing the usage of LAMBDA function  showing the usage of LAMBDA function

Now, you can use the new formula name in a cell to get the calculation result.
showing the usage of LAMBDA function


Other Functions:

  • Excel bycol Function
    The Excel BYCOL function applies a LAMBDA function to each column in a given array and returns the result per column as a single array.

  • Excel BYROW Function
    The Excel BYROW function applies a LAMBDA function to each row in a given array and returns the result per row as a single array.

  • Excel Z.TEST Function
    The Excel Z.TEST function (2010) returns the one-tailed P-value of a z-test which is useful for various analyses.

  • Excel F.DIST Function
    The Excel F.DIST function returns the F probability distribution which is usually used to measure the degree of diversity between two data sets.


The Best Office Productivity Tools

Kutools for Excel - Helps You To Stand Out From Crowd

🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions
Popular Features: Find, Highlight or Identify Duplicates  |  Delete Blank Rows  |  Combine Columns or Cells without Losing Data  |  Round without Formula ...
Super VLookup: Multiple Criteria  |  Multiple Value  |  Across Multi-Sheets  |  Fuzzy Lookup...
Adv. Drop-down List: Easy Drop Down List  |  Dependent Drop Down List  |  Multi-select Drop Down List...
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  Compare Columns to Select Same & Different Cells ...
Featured Features: Grid Focus  |  Design View  |  Big Formula Bar  |  Workbook & Sheet Manager | Resource Library (Auto Text)  |  Date Picker  |  Combine Worksheets  |  Encrypt/Decrypt Cells  |  Send Emails by List  |  Super Filter  |  Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets12 Text Tools (Add Text, Remove Characters ...)  |  50+ Chart Types (Gantt Chart ...)  |  40+ Practical Formulas (Calculate age based on birthday ...)  |  19 Insertion Tools (Insert QR Code, Insert Picture from Path ...)  |  12 Conversion Tools (Numbers to Words, Currency Conversion ...)  |  7 Merge & Split Tools (Advanced Combine Rows, Split Excel Cells ...)  |  ... and more

Kutools for Excel Boasts Over 300 Features, Ensuring That What You Need is Just A Click Away...


Office Tab - Enable Tabbed Reading and Editing in Microsoft Office (include Excel)

  • One second to switch between dozens of open documents!
  • Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
  • Increases your productivity by 50% when viewing and editing multiple documents.
  • Brings Efficient Tabs to Office (include Excel), Just Like Chrome, Edge and Firefox.