How to Build Your Own Excel Functions with the LAMBDA Function
(Note: Suitable for users of Excel 2021 and Excel for Microsoft 365.)
Objective
Use the new LAMBDA function to build your own Excel functions.
LAMBDA Function Explained
The new LAMBDA function in Excel is a game-changer! It allows us to create custom functions that can be called by user-friendly names and reused throughout the workbook.
So now, we can take an existing complex formula, wrap it up in a LAMBDA function and give it any name we like. Instead of typing in the long formula, we can simply recall the formula using the name we have given it.
For example, look at this long SORTBY formula:
=SORTBY(unique(C4:C19),SUMIFS(D4:D19,C4:C19,unique(C4:C19)),-1)
Notice in this formula we are using two cell ranges, C4:C19 and D4:D19. The former is used multiple times in the formula.
Similar to the LET function, we could declare the variables and give these cell ranges more meaningful names. Maybe the cell range C4:C19 refers to the data range so we could call it datarng. Maybe D4:D19 refers to the product range so we could call it productrng. We can then use these names in the formula instead of the cell references.
=SORTBY(unique(datarng),SUMIFS(productrng,datarng,unique(datarng)),-1)
Once we have a formula that is using our declared variables, we can then create our own custom function. This is where the power of LAMBA lies!
Using the Name Manager, we can give this entire complex formula a user-friendly name, e.g., SumofProduct. Every time we need to reuse this formula in the workbook, we can recall it by the name.
Related reads:
How to Use Excel MATCH Function
How to Graph a Function in Excel
How to Use SUMPRODUCT Function in Excel
Simple LAMBDA in Action
In this first example, we’ll take a look at a simple LAMBDA.
In the screenshot below, we have a small table. In column B we have some sales prices and in column C, we need to work out what the price is with 20% tax added. A pretty simple calculation.
NOTE: I’ve calculated the total this way as we don’t have 20% written in a cell to reference. We could also calculate this as =SUM(B8*20%)+B8.
This is a very simple formula, but we could convert this into a LAMBDA function. We could then set it up as a function and reuse it whenever we want to increase prices by a certain percentage (it doesn’t have to be 20% each time, it could be any value).
Create a LAMBDA
Let’s now set this simple calculation up as a LAMBDA.
- Click in cell D8 and type =LAMBDA
The syntax for the LAMBDA function is as follows:
=LAMBDA(parameter_or_calculation,…)
The first thing we need to define for our LAMBDA function is our parameters. Let’s start by defining one parameter. My first parameter is going to refer to the value in cell B8. You can name your parameter whatever you like. I’m going to call mine, x. Effectively x will equal the value in cell B8.
I mentioned before that LAMBDA’s are similar to LET’s, but there is one major difference.
If we were using LET, we would set up what value is attributed to the parameter in the next argument. However, when we are creating a LAMBDA function, we define the value for the parameter at the end of the formula.
Instead, we define our calculation next.
So, x represents the calculation x*(1+20%). Remember, x = cell B8.
If we press Enter on the above calculation, we’ll receive a #CALC error. This is because we haven’t told the formula what x represents. Excel doesn’t know that x represents cell B8. We need to add this at the end of the formula in brackets.
We can now drag this formula down and the calculation is correct.
Let’s add a second variable to represent 20%. I’m going to name my second variable, y. At the end of the formula, we need to tell Excel what y represents. In this case, 20%.
We can then replace the 20% in the formula, with the variable.
The formula is correct because we get exactly the same result.
Create a Function
Now that we have our LAMBDA function, we can create a function using the Name Manager and reuse the formula elsewhere in the workbook.
When we create the function, we only need the part of the formula that processes the calculation.
- Select the first part of the formula and press Ctrl+C to copy.
- Click on the Formulas tab.
- In the Define Names group, click Name Manager.
- Click New.
- Give the formula a name ‘PricePlusTax’.
- Click in Refers to and press Ctrl+V to paste the formula.
- Click OK and OK again.
Call the Function
Now, instead of performing the calculation we can simply call the function.
- Clear the cells in column D.
- In cell D8, type =PricePlusTax
Notice that the new function will show in Excel’s Intellisense so we can select it from the list. The function arguments will be the parameters we specified in the LAMBDA. This is why it’s a good idea to think of better names for your arguments than x and y.
Now, we simply need to specify what values we want to use for x and y. Our x values are the sales price and our y values are the tax rate. We don’t have to use 20% as our tax rate so let’s use 40% instead.
- Press Enter.
Suggested reads:
How to Create an Excel Gauge Chart the Easy Way
Battle of the Excel Lookup Functions: VLOOKUP vs INDEX/MATCH vs XLOOKUP
Complex LAMBDA
Now we’ve seen how a LAMBDA function works, let’s create a more complex formula.
In this example, I want to sort the table of data by the salary column in ascending order and then by the region column in ascending order.
We can use the new SORTBY function to do this.
Let’s take a look at this calculation.
=SORTBY(A4:D17,B4:B17,1,D4:D17,1)
We want to sort the whole table of data (A4:D17) by the Salary column (B4:B17) in Ascending order (1) and then by the Region column (D4:D17) in Ascending order (1).
We can set the SORTBY formula up as a LAMBDA function. Let’s look at the formula more closely.
We are using three different cell ranges in the formula, A4:D17, B4:B17 and D4:D17.
=SORTBY(A4:D17,B4:B17,1,D4:D17,1)
We can set these up as variables. This time, we will use more meaningful variable names.
A4:D17 – data
B4:B17 – salary
D4:D17 – region
- Click in cell G4 and edit the formula.
- Add the LAMBDA function and the variables to the beginning of the formula.
Now we need to tell Excel what cell ranges those variables refer to. We must define the cell ranges in the same order as the variable names at the end of the calculation.
Now we can edit the formula and replace the cell references with the names.
The final step is to create the function for this formula.
- Select the first part of the formula and press Ctrl+C to copy.
- Click on the Formulas tab.
- In the Define Names group, click Name Manager.
- Click New.
- Give the formula a name ‘SortByNumCat’.
- Click in Refers to and press Ctrl+V to paste the formula.
- Click OK.
Let’s test the new function.
- Clear the sorted results from cells G4:J17.
- In cell G4, type =SortByNumCat
Note the arguments are the names we assigned to our variables. Now, we simply need to provide the cell references for data, salary and region.
- Select the cell ranges.
- Press Enter.
We can use the SortByNumCat function on similar data.
The data is now sorted by Price in Ascending order and then by Location in Ascending order.
Also read:
Ten Ways to Clean Data in Excel
Please visit our free resources center for more high-quality Excel and Microsoft Suite application guides.
Ready to dive deep into Excel? Click here for advanced Excel courses with in-depth training modules.
Simon Sez IT taught Excel and other business software for over ten years. You can access 150+ IT training courses for a low monthly fee.
Deborah Ashby
Deborah Ashby is a TAP Accredited IT Trainer, specializing in the design, delivery, and facilitation of Microsoft courses both online and in the classroom.She has over 11 years of IT Training Experience and 24 years in the IT Industry. To date, she's trained over 10,000 people in the UK and overseas at companies such as HMRC, the Metropolitan Police, Parliament, SKY, Microsoft, Kew Gardens, Norton Rose Fulbright LLP.She's a qualified MOS Master for 2010, 2013, and 2016 editions of Microsoft Office and is COLF and TAP Accredited and a member of The British Learning Institute.
