Skip to Content

Excel LAMBDA Function: Ultimate tool to Create Custom Functions in Excel

The LAMBDA is a prebuilt function in Excel. It is used to create custom functions. Its main goal is to let users automate tasks in Excel without any VBA and macros expertise. 

So, gone are the days when only programmers could create custom functions in Excel. Nowadays, anyone can easily create their own function using Excel’s formula language.

Suppose that you are an HR manager. Every year during Christmas, you reward employees with a bonus equal to 15% of their salary. In order to get the bonus, they should have:

  • Performance rating: 4+
  • Years of Service: 5+

Remaining employees get a flat $1,000 bonus.

You use the IF function in Excel to solve this problem.

=IF(AND(D2>=4,C2>=5), B2*0.15, 1000)

Every year, you need to recall this lengthy formula and type it manually. But what if you could save and reuse the entire formula by a short name?

That’s where the Excel LAMBDA function comes into play.

=LAMBDA(salary, rating, years, IF(AND(rating >= 4, years >= 5), salary * 0.15, 5000))

The beauty of the above formula is that you can save it using the Name Manager feature in Excel. I will name it ChristmasBonus. Once saved, I can reuse the same function throughout the given workbook just by typing:

=ChristmasBonus

Syntax

The LAMBDA is a powerful function with the following syntax. You can combine it with other functions in Excel to create complex formulas and automate tasks.

=LAMBDA(parameter_or_calculation, …)

Where,

  • parameter is an input value
  • calculation is a condition or equation

Important Notes:

  • You can input up to 253 parameters
  • You can define only one calculation
  • The function is not case-sensitive
  • It does not support wildcards
  • Returns #CALC error if the incorrect number of arguments are passed
  • You cannot include the links to external workbooks in the formula

How to use Name Manager in Excel

It’s a built-in tool in Excel that lets you define custom names for ranges, formulas, and constants.

You can easily manage user-defined names within the given workbook by going to Formulas > Defined Names > Name Manager. Refer to the following screenshot.

Refer to the following screenshot.

How to use the LAMBDA Functino in Excel

Download the example spreadsheet used to demonstrate the LAMBDA function in the upcoming section. Practice is the key to success!

Download Now

Task

Consider the following data. We have sales reps’ details, including their names, base salaries, and years of service. We also have the details of region-wise sales targets they could achieve in the previous month.

Their monthly salary depends on the following factors:

  • Sales target achievement
  • Assigned region
  • Years of service

Commissions based on sales target achievement are as follows:

  • 50% of their base salary if the achievement is greater than 120%
  • 25% of their base salary if the achievement is greater than 100%
  • 10% of their base salary if the achievement is greater than 75%
  • 0% commission if the achievement is below 75%

Bonus based on region:

  • $5,000 for North
  • $4,000 for South
  • $2,000 for East
  • $1,000 for West

Loyalty bonus based on years of service

The loyalty bonus is $1,000 per month if the employee has completed 1 year in our company. It increases by $1,000 each year for the next 10 years.

Now, let’s begin. We will first calculate the incentive (additional amount based on the above conditions) using the LAMBDA function. Then, in the next column, we will simply add base salary and incentive to get the final payout.

STEP 1: Build Your Formula

This is a crucial step that includes building a raw formula. It will be saved using the LAMBDA function and Name Manager feature in Excel.

We will create a formula to calculate the incentive using the conditions above. Our formula would be:

=IF(D2>=120%, B2*0.5, IF(D2>=100%, B2*0.25, IF(D2>=75%, B2*0.1, 0))) + XLOOKUP(C2, {"North", "South", "East", "West"}, {5000, 4000, 2000, 1000}) + MIN(E2*1000, 10000)

Where,

  • IF(D2>=120%, B2*0.5, IF(D2>=100%, B2*0.25, IF(D2>=75%, B2*0.1, 0))) is the nested IF statement to calculate commission based on sales target achievement
  • XLOOKUP(C2, {“North”, “South”, “East”, “West”}, {5000, 4000, 2000, 1000}) is the formula to calculate the bonus based on the region
  • MIN(E2*1000, 10000) is the formula to calculate loyalty bonus based on years of service

If we put the above formula in column F, we will get the incentive as shown below.

The above formula is both hard to remember and write manually every month while calculating the monthly salary of the given sales reps.

STEP 2: Build the LAMBDA version of the formula

It requires parameters and a calculation. We already created a formula (calculation) in the previous section. Now, let us get the parameters.

  • base – It is the parameter for the base salary
  • experience – It is the parameter for the years of experience
  • region – It is the parameter for the region of sales
  • achievement – It is the parameter for the sales target achievement.

Our Lambda version of the formula will be:

=LAMBDA(base, region, achievement, experience,
    IF(achievement>=120%, base*0.5,
    IF(achievement>=100%, base*0.25,
    IF(achievement>=80%, base*0.1, 0)))
    +
    XLOOKUP(region, {"North", "South", "East", "West"}, {5000, 4000, 2000, 1000})
    +
    MIN(experience*1000, 10000)
)

For the sake of simplicity, I have broken down the above formula into different lines. The formulas in Excel are mostly written on one line. For example,

=LAMBDA(base, region, achievement, experience, IF(achievement>=120%, base*0.5, IF(achievement>=100%, base*0.25, IF(achievement>=75%, base*0.1, 0))) + XLOOKUP(region, {"North", "South", "East", "West"}, {5000, 4000, 2000, 1000}) + MIN(experience*1000, 10000))

If we put this formula as is in column F, we will get an error.

This error is caused by the function being unable to find the parameters. The solution is to add the cell references to the formula as shown below.

=LAMBDA(base, region, achievement, experience,
    IF(achievement>=120%, base*0.5,
    IF(achievement>=100%, base*0.25,
    IF(achievement>=80%, base*0.1, 0)))
    +
    XLOOKUP(region, {"North", "South", "East", "West"}, {5000, 4000, 2000, 1000})
    +
    MIN(experience*1000, 10000)
)(B2,C2,D2,E2)

Now, it works fine.

Note that you can leverage the LET function in Excel to name the calculation results within the formula. It will help us easily understand each part of the formula.

=LAMBDA(base, region, achievement, experience,
    LET(
       commission,
          IF(achievement>=120%, base*0.5,
          IF(achievement>=100%, base*0.25,
          IF(achievement>=80%, base*0.1, 0))),
       regionBonus,
          XLOOKUP(region, {"North", "South", "East", "West"}, {5000, 4000, 2000, 1000}),
       loyaltyBonus,
          MIN(experience*1000, 10000),
       total,
          commission + regionBonus + loyaltyBonus,
       total
       )
)

STEP 3: Create a Custom Function

In this step, we will name our LAMBDA version of the formula. That name will be reused in the future to calculate the incentive within a few seconds.

  • Go to Formulas from the ribbon
  • In the Defined Names section
  • Click on the Name Manager icon
  • A dialog box will appear on your screen as shown in the following image
  • Click the New button
  • A new popup will appear
  • Put a desired name in the Name box as shown below
    (I have put “INCENTIVE”)
  • Make sure that the scope is set to Workbook
  • Copy and paste the LAMBDA version of the formula in the Refers to box as follows
  • Click the OK button to close the popup
  • You will be redirected to the Name Manager dialog box
  • Click the Close button

Important Notes:

  • The name can be a maximum of 255 characters long
  • It must start with the letter (A-Z, a-z) or an underscore (_)
  • Function names do not contain spaces or special characters like @, !, #, $, %, etc.
  • Make sure to choose a unique name to avoid potential conflict with the existing functions
  • Avoid using cell references like A1, B1, D2, R100, etc. in your custom name

STEP 4: Use your Custom Function

Using your custom formula is pretty straightforward. The process is similar to using a pre-built function in Excel.

Here are the steps,

  • Select the desired cell
  • Type =INCENTIVE
  • Choose the first option from the popup
  • Enter the parameters
    (In this case, parameters act as arguments in prebuild functions)
  • Close the brackets using )
  • Hit Enter

Once we have incentives in column F, it is time to calculate the total payout. As discussed earlier, we will add incentives and base salaries in a separate column. Refer to the following GIF.

How to Export or Import Custom or LAMBDA Functions to Another Workbook

As we have already discussed, the custom functions can be used across various sheets from the given workbook only.

There’s no direct way to share them across different workbooks.

But here’s the trick. You can transfer custom functions from one workbook to another by copying a blank (or any sheet) from the old workbook to the new one.

Steps to copy a sheet from one workbook to another:

  • Right-click the desired sheet name as shown below
  • Choose the Move or copy option from the pop-up
  • A new dialog box will appear on your screen
  • In the To book box, choose the target workbook
  • Make sure to select the Move to end option as shown in the following image
  • Tick the box before Create a copy option
  • Press the OK button

With these steps, all custom functions will be transferred to the target workbook.

How to edit Custom Functions created using the LAMBDA function

Editing your custom function is really easy. You can do it using the Name Manager.

All custom functions appear in the Name Manager dialog box, as shown in the image below.

You can simply select the desired function and click the Edit button to make changes. It allows you to change:

  • Name
  • Core Formula

You can also add comments for future reference.

To delete the function, use the Delete button at the top.

How to Get Rid of the Errors

Here’s a quick list of errors that you may encounter while using your custom or LAMBDA function.

  • #VALUE – If the names used in the calculation do not match the parameters defined at the beginning of the LAMBDA function, you will get a #VALUE error. The same error is also displayed when you have specified an incorrect number of parameters/arguments.
  • #NUM – It is a rare error that occurs when a recursive LAMBDA function enters a loop.
  • #CALC – This is the most common error when using the LAMBDA function. It occurs when you provide incorrect parameters.
  • #NAME – This type of error is displayed when you are using older versions of Excel.

FAQs

Q. What is the LAMBDA function in Excel?

The LAMBDA is a prebuilt logical function in Excel. It allows you to create custom formulas that can be reused across the given workbook.

Think of it as a handy tool to create your own functions/formulas in Excel without any coding expertise. You don’t need to be an expert at VBA and Macros anymore.

Q. How do I Create a Custom Function in Excel?

Use the built-in LAMBDA function to create custom functions in Excel. It allows you to create custom functions that can be reused any time. You don’t need to be an expert at VBA or Macros to leverage this feature in Excel.

Takeaway

The LAMBDA offers an excellent way to build custom functions in Excel. It is a powerful tool for people who don’t know how to code.

It offers excellent flexibility as well. Suppose your criteria change in the future, the custom function can be easily edited using the Name Manager feature. All the changes instantly get reflected across all the sheets.

I hope the above article helped you build your first custom function in Excel. If you are stuck somewhere or are having any particular error while using the LAMBDA function and Name Manager in Excel, please comment below. I look forward to answering all your questions as soon as possible.

Additional Resources:

  • Learn All Excel Logical Functions (With Examples)
  • IF Function in Excel
  • MAP Function in Excel
  • LET Function in Excel
  • AND Function in Excel
  • OR Function in Excel
  • NOT Function in Excel

Get an Office 365 Subscription to Access all the powerful Functions and Tools in Excel.