Excel LAMBDA Function

The new Excel LAMBDA function allows you to define your own custom functions using Excel’s familiar formula language. That means we no longer need JavaScript or VBA programming knowledge to create our own functions.

This is a huge step forward and for those familiar with the concept of lambdas it’s worth noting that the LAMBDA function makes Excel’s formula language Turing Complete.

Note: Currently the LAMBDA function is only available to Microsoft 365 users on the Office Insider beta channel. It will be rolled out to all Microsoft 365 users over time, but it will not be made available in Excel 2019 or earlier.

Watch the Video

Download Workbook

Excel LAMBDA Function Syntax & Example

The easiest way to learn how the LAMBDA function works is with an example. The image below steps through the syntax and on to a basic example.

Authoring and Debugging Lambdas

There aren’t many tools available to help with authoring your lambda functions yet, therefore it is easiest done in a cell in the Excel grid. We can evaluate the lambda without having to define a name by entering the variables in parentheses after the formula like so:

You can see the formula evaluates to 5 in the grid and in the formula bar I’ve entered the variables 2 and 3. You can also reference cells etc. when testing:

Once you’re happy the function is correct, you’re ready to define it as a named formula.

Define LAMBDA Named Formula

While we can enter and evaluate a lambda formula directly in a cell (as shown above), the best place for your lambdas is to define a name. By naming it, we can then call the Lambda from anywhere in the workbook. Defining a name is done via the Formulas tab > Define Name:

Note: Your lambda is specific to a workbook. If you want to use it in other workbooks you need to copy them across. If you copy a sheet that contains a reference to a lambda function into a new workbook it will be automatically copied to the name manager for use there. This is great, however be careful because you could inadvertently copy lambdas to new workbooks that you don’t need, thus creating lambda hell in the Name Manager.

Calling Lambdas

Once you’ve created your lambda and defined it as a name, they work like any other function in that you simply prefix your lambda name with an equal sign and wrap the arguments in parentheses:

Unfortunately, there’s no itellisense for lambdas yet.

Easy LAMBDA Examples

There are often easy formulas you regularly use that can be super handy as a lambda. For example, adding GST/VAT/Sales Tax, or calculating the amount net of GST/VAT/Sales Tax.

Referencing Other Functions in LAMBDAs

So far, we’ve only looked at basic lambda functions, but we can also use them with other functions. Let’s say I want to create a custom function that tests whether a value or date falls within a range. I’ll call it BetweenInclusive because it’s going to include the upper and lower bands in the range.

It’s actually easy to create a BETWEEN formula using the MEDIAN function, but that’s not obvious to most Excel users so this is a good example of where lambdas can be super helpful to less experienced users.

Here I’ve got a list of values I want to test in column B, and the lower and upper limits in columns C and D:

I can test if the value in cell B5 falls between the lower and upper limits using this formula:

=B5=MEDIAN(B5, C5, D5)

Which returns:

=FALSE

To convert this to a lambda I need to declare (name) the variables for the Value, Lower and Upper and then replace the cell refences in the calculation with my newly declared names, as shown below:

=LAMBDA(

Val, Low, Up, 

Val=MEDIAN(Val, Low, Up)

)

Tip: Notice I’ve avoided names that are the same as existing function names like VALUE, LOWER and UPPER. If you use existing function names Excel can get confused as to whether you’re referring to the function or your variable.

I can test this in the worksheet cells by specifying the variables at the end:

=LAMBDA(

Val, Low, Up, 

Val=MEDIAN(Val, Low, Up)

)(6,6,10)

Once I’m happy with the formula I can copy it to the Name Manager:

Tip: enter a comment that describes how your function works. This will appear as a tooltip when using the function.

Now when I call my custom lambda function in a cell you can see it appears in the list of functions with a different icon to differentiate it from the built-in functions. You can also see the tooltip I entered in the comments:

Note: Unfortunately, the function wizard doesn’t work for custom LAMBDA functions, so documenting your function in the comments is important until such time that we have something better.

You can see the results of my lambda in action in the image below:

LAMBDA and LET Together

LAMBDA loves LET and LET loves LAMBDA! These functions work great together. Let’s look at an example.

Income tax is often calculated on a bracket system. Here in Australia, we have the following tax brackets for 2021:

Calculating the tax for someone with an income of $190,000 requires a large, complex nested if formula:

=IF(190000<=D5,0,

IF(190000<=D6,(190000-D5)*E6,

IF(190000<=D7,F6+(190000-D6)*E7,

IF(190000<=D8,F6+F7+(190000-D7)*E8,

IF(190000>D8,F6+F7+F8+(190000-D8)*E9)
  ))))

Formulas like this are difficult to use and easily broken which makes them perfect for creating as a custom function.

In the expanded table below, you can see I’ve inserted the variable names in row 4 and added a column (F) for the Tax Amount for the upper limit of each bracket. This just helps me author my lambda in the grid.

I already have the IF formula written and I only have one input which is the taxable income. It’s easy to convert this to a custom LAMBDA function by declaring my variable, which I’ll call ‘ti’ for taxable income.

I’ll call this lambda IncomeTax:

=LAMBDA(ti,

IF(ti<=D5,0,

IF(ti <=D6,( ti -D5)*E6,

IF(ti <=D7,F6+( ti -D6)*E7,

IF(ti <=D8,F6+F7+( ti -D7)*E8,

IF(ti >D8,F6+F7+F8+( ti -D8)*E9)))))

)

However, there’s still a lot of repetition in this function with the same cells being referenced repeatedly. What would make this even better is to use the LET function to declare the upper limit, the tax rate and tax amount variables for each bracket (Bracn, Raten and TaxAn):

If required I can easily update my custom function by editing the variables in the name manager. These changes feed through to any formulas that use my function in the current file.

Note: If you’ve used this function in other files, you’d need to open those files and update them too.

Now calculating the income tax is as simple as entering or referencing a cell containing the taxable income:

LAMBDA Limitations

It’s early days for the Excel LAMBDA function and the authoring experience needs some work. The Excel team at Microsoft are aware of this and have a ton of improvements planned to address these shortfalls. If you want to give feedback you can do so through the Help tool and tag your comments with #LAMBDA or post in the Excel Tech Community.

Backward compatibility

While the LAMBDA function will never be available in Excel 2019 or earlier, workbooks containing lambda formulas will still show the results of custom lambda functions when opened in earlier versions. However, if the user edits the cell in Excel 2019 or earlier the formula will then return an error.

More Excel LAMBDA Functions

This is just the beginning for lambdas. You might like to try them with dynamic arrays or data types. You can even do recursion, but that’s for another day!