• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Excel LAMBDA Function

You are here: Home / Excel Formulas / Excel LAMBDA Function
Excel LAMBDA Function
February 25, 2021 by Mynda Treacy

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

Subscribe YouTube

Download Workbook

Enter your email address below to download the sample workbook.

By submitting your email address you agree that we can email you our Excel newsletter.
Please enter a valid email address.

Download the Excel Workbook and follow along. Note: This is a .xlsx file please ensure your browser doesn't change the file extension on download.

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.

Excel LAMBDA function syntax

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:

Excel LAMBDA function example

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:

Excel LAMBDA function variables

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:

Define a 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:

calling lambdas

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.

easy custom lambda functions

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:

between formula

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:

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:

custom function tooltip

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:

Excel Between formula

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:

Income tax brackets

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.

Income tax brackets

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):

Income tax brackets

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 with LET function

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!

Excel LAMBDA Function

More Excel Formulas Posts

ai-aided excel formula editor

AI Aided Excel Formula Editor

Save time with this free AI Excel formula editor add-in that writes, edits, improves and interprets formulas for you!
top excel functions for data analysts

Top Excel Functions for Data Analysts

Must know Excel Functions for Data Analysts and what functions you don’t have to waste time learning and why.
excel advanced formula environment

Excel Advanced Formula Environment

Excel Advanced Formula Environment is a long awaited, new improved way to write, name and store Excel formulas.
Pro Excel Formula Writing Tips

Pro Excel Formula Writing Tips

Must know Excel formula writing tips, tricks and tools to make you an Excel formula ninja, including a new formula editor.
excel shaping arrays

New Array Shaping Excel Functions

The Excel Shaping Array Functions makes it easier than ever to reshape arrays and ranges using these purpose built functions
excel nested if functions what not to do

Excel IF Formulas and What Not To Do

Excel IF formulas can get out of hand when you nest too many IFs. Not only do they become unwieldy they’re difficult for anyone to understand
excel image function

Excel IMAGE Function

The Excel IMAGE Function enables you to embed images in a cell using a formula. It supports BMP, JPG/JPEG, GIF, TIFF, PNG, ICO, and WEBP files

Excel VSTACK and HSTACK Functions

New Excel VSTACK and HSTACK functions makes combining arrays of cells easy and with some clever tricks we can extend their capabilities.
identify overlapping dates and times in excel

Identify overlapping dates and times in Excel

How to identify overlapping dates and times in Excel with a formula that checks a range of cells. Works with Dates and Times.
New Excel Text Functions

TEXTSPLIT, TEXTBEFORE and TEXTAFTER Functions

TEXTAFTER, TEXTBEFORE and TEXTSPLIT are exciting new Excel Text functions. They’re fairly self-explanatory, however TEXTSPLIT has some cool features.


Category: Excel Formulas
Previous Post:sort by column in power biSort-By Columns in Power BI
Next Post:Recursive LAMBDA FunctionsExcel LAMBDA Recursion

Reader Interactions

Comments

  1. JAMSHAID

    August 1, 2022 at 8:41 pm

    CAN YOU PLEASE EXPLAIN A WAY TO ASK LAMBDA ARGUMENT AS NOT BLANK,

    Reply
    • Mynda Treacy

      August 2, 2022 at 9:38 am

      Please post your question on our Excel forum where you can also upload a sample file and we can help you further.

      Reply
  2. TB

    February 26, 2021 at 3:54 am

    Hi Mynda,

    In lieu of a proper development environment, I’ve been taking advantage of the Excel 4.0 EVALUATE macro to be able to debug LET/LAMBDA from a worksheet. Once I’m satisfied with my formula, I migrate it to the Name Manager, see the thread here: https://techcommunity.microsoft.com/t5/excel/legacy-evaluate-in-lambda-build-test-lambdas-from-sheet/m-p/2133924

    Hopefully this helps some folks until Microsoft gives us something better to work with.

    Reply
    • Mynda Treacy

      February 26, 2021 at 8:49 am

      Thanks for sharing the link, TB. That is an interesting approach for sure, but still a little laborious. An easier way for non-recursive lambdas is to provide the variables in parentheses after the lambda. I gave an example in the post above:

      =LAMBDA(x,y.x+y)(2,3)

      For recursive lambdas I have another cell based solution that I’ll post next week when I cover recursive lambdas 😉

      Mynda

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Current ye@r *

Leave this field empty

Sidebar

More results...

Featured Content

  • 10 Common Excel Mistakes to Avoid
  • Top Excel Functions for Data Analysts
  • Secrets to Building Excel Dashboards in Less Than 15 Minutes
  • Pro Excel Formula Writing Tips
  • Hidden Excel Double-Click Shortcuts
  • Top 10 Intermediate Excel Functions
  • 5 Pro Excel Dashboard Design Tips
  • 5 Excel SUM Function Tricks
  • 239 Excel Keyboard Shortcuts

100 Excel Tips and Tricks eBook

Download Free Tips & Tricks

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

Guides and Resources

  • Excel Keyboard Shortcuts
  • Excel Functions
  • Excel Formulas
  • Excel Custom Number Formatting
  • ALT Codes
  • Pivot Tables
  • VLOOKUP
  • VBA
  • Excel Userforms
  • Free Downloads

239 Excel Keyboard Shortcuts

Download Free PDF

Free Webinars

Excel Dashboards Webinar

Watch our free webinars and learn to create Interactive Dashboard Reports in Excel or Power BI

Click Here to Watch Now

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk – For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.