Excel LAMBDA functions

Image of software function sign fx

In Excel version 365, create custom, reusable functions in Excel using LAMBDA functions.

Here we describe how to create a custom Excel Lambda function and then save it in the Name Manager for use throughout a workbook. LAMBDA functions don’t require knowledge of VBA, macros or JavaScript, and are similar to the LET function. Unfortunately though they are only available in Excel 365.

Excample of a simple Excel Lambda function
Links to topics on this page

Excel Lambda function syntax

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

For example, we’ve created a custom function named get_first_letter().

The function is: =LAMBDA(x, LEFT(x, 1)), where the parameter is ‘x’ and the ‘calculation’ is LEFT(x, 1).

Below shows how we call the function from a cell in the worksheet. We’re inputting the value held in Cell C9 (‘Hello’), and the function returns the first letter of the string ‘H’.

Example of an Excel Lambda function to extract the first letter of a string input

Steps to create and use an Excel LAMBDA function

  1. Test the function – create a simple formula in a worksheet cell. Try not to jump the gun and head straight for the LAMBDA function, test out your formula first in a cell.
  2. Insert the function into Excel’s LAMBDA formula syntax, and test again in a cell in the worksheet. To test the function (before saving and naming it in step 3 below) try it out first directly in the worksheet – create it AND call it from within the same cell. E.g. =LAMBDA(x, x+5)(6). [result = 11]; you’ll get an error if you try to create it without calling it, see Errors with Lambda functions below.
  3. Add the LAMBDA formula to the Name Manager so that it can be reused throughout the workbook, or on individual worksheets. At this stage you give your custom Lambda function a user-friendly name so that you can call it from cells in your worksheet/workbook.
  4. To use the Excel LAMBDA function, call it from a cell in the workbook. When you start to type the name of your new function after ‘=’ in the cell, you’ll be able to select it from the list of functions in the prompt list.
Excel's Name Manager found on the Formulas tab in the ribbon

Naming a Lambda function in Excel 365

  • Open the Name Manager (from the Formulas tab in the ribbon) and select ‘New’.
  • Follow Excel’s syntax rules for names, EXCEPT don’t use a period (.).
  • Use upper, lower case letters, symbols, numbers.
  • If you prefix your custom functions with the same combination (e.g. ‘myLambda_’) then when you want to call them, they will all appear together in the Excel Native functions prompt list – helpful if you forget what you have called your functions 🙂
  • The comment that you add for the function details will appear in a tooltip when you select the function.
  • You can also edit or delete your custom functions from the Name Manager.
Shows the Lambda function comment details will appear as a tooltip next to the function name in the Excel functions list.

Inputting number and text values into Lambda functions

You can input number and string values into Lambda functions, and values can be:

  1. fed in directly (in the same cell as where the function is called), or
  2. read in via a cell reference.
Input values into Lambda functions either directly or via cell references.

When inputting strings into a function directly, remember to wrap the values inside quotes [single ”, or double “”], otherwise you’ll get a #NAME? error.

Inputting a string value into a Lambda function

Error results using Excel Lambda functions

  • If you create a LAMBDA function in versions of Excel other than 365, you’ll get a #NAME? error when you try to call the function. So although you seem to be able to create the function and save it in the Name Manager, you can’t actually call the function.
  • #VALUE! error, if:
    • you enter more than 253 parameters;
    • an incorrect number of arguments is passed to a LAMBDA function.
  • Calling a LAMBDA function from within itself and if the call is circular, Excel can return a #NUM! error, if there are too many recursive calls.
  • Creating a LAMBDA function in a cell without also calling it from within the cell, Excel returns a #CALC! error.

>> On the next page we look at how to share and copy Lambda functions between Excel workbooks, and the potential complications that can arise from doing so!

Pages: 1 2