by Svetlana Cheusheva, updated on
The tutorial explains the Excel LAMBDA function in a simple language and demonstrates a few real-life examples of its use, behavior and possible pitfalls.
Until recently, user-defined functions were the prerogative of programmers. The introduction of LAMBDA has made them available for everyone. What is Excel LAMBDA? In essence, it's a function to create other functions. With this wonderful addition to Excel's toolbox, we no longer need VBA skills to do complex computations, instead we can define our own functions using a familiar formula language. So, let's dive in and get good use out of it!
The LAMBDA function in Excel is designed to create custom functions that can be called by user-friendly names and reused throughout a workbook.
Simply put, you can now take any existing formula, whatever complex it is, wrap it up in LAMBDA, and give it any name you like, say BestFunction. And then, instead of typing your original lengthy formula, you can refer to BestFunction() anywhere in your workbook.
The LAMBDA function works without macros, VBA or JavaScript, so every user and not just programmers can benefit from it.
The lambda symbol (λ) representing the 11th letter of the Greek alphabet is often used in physics, mathematics and computer science, where it carries different meanings.
In computer science, the concept of lambdas dates back to lambda calculus (λ-calculus) introduced by American mathematician and logician Alonzo Church in the 1930s. At heart, it is a universal model of computation in which all functions are deemed anonymous and can be formed by abstraction.
Microsoft announced LAMBDA in December 2020 proudly claiming that the new function makes Excel Turing-complete (i.e. computationally universal) and allows users to perform almost any calculations in the native formula language. How is that possible? Due to the fact that a LAMBDA-defined function can call other functions or even itself as many times as needed. This feature is called "recursion", and this is what makes LAMBDA so effective. Earlier, recursion in Excel was only possible through VBA or Office Script.
The Excel LAMBDA function has the following syntax and arguments:
Where:
Parameter (optional) - an input value that can be supplied in the form of a cell reference, number, or text string. The function accepts up to 253 parameters.
Calculation (required) - the formula to execute or calculation to perform. It must be the last argument and it must return a result.
Here is an example of a custom LAMBDA function in its simplest form:
The below guidelines will increase your chances of building a perfectly working custom function at the first attempt and help avoid common errors:
The LAMBDA function is only available in Microsoft 365 subscriptions including:
To create a Lambda function in your workbook, these are the steps to perform:
In most cases, you begin with writing a core formula that returns the desired result. To focus on the process of the LAMBDA creation, we'll keep the formula's logic very simple.
As an example, let's take the classic percentage variance formula:
With cell references, it takes this form:
=C2/B2-1
To prevent #DIV/0! errors when dividing by a zero value, we wrap it in the IFERROR function like this:
=IFERROR(C2/B2-1, "-")
As you can see in the below screenshot, our formula works as intended, so we are ready to move on to the next step:
If your formula requires input values, add them as parameters to the LAMBDA function. Our sample formula calculates the percent change between 2 numbers, so we declare 2 parameters:
=LAMBDA(old, new
Next, add the formula to the calculation argument. Please notice that instead of cell references we supply the declared parameters:
=LAMBDA(old, new, IFERROR(new/old-1, "-"))
If entered in a cell at this point, our formula will return a #CALC! error because it has no values to process. For testing purposes, you should provide the input values in an additional set of parentheses after the formula:
=LAMBDA(old, new, IFERROR(new/old-1, "-"))(B2, C2)
This testing syntax allows calling the LAMBDA function from within a cell before naming it:
The screenshot below proves that the results returned by LAMBDA are no different from the original formula:
After successful testing, you are ready to name your LAMBDA. Here's how:
=LAMBDA(old, new, IFERROR(new/old-1, "-"))
Tip. To edit the formula in the Refers to box, press the F2 key to switch from Enter to Edit mode. This will let you navigate through the formula by using the arrow keys without breaking it.
The syntax rules for LAMBDA names is essentially the same as for other Excel names, please see Excel naming rules.
As soon as your LAMBDA function gets a name, you can refer to it like you would any native function. Our Lambda is named PercentVar and it requires 2 arguments - the old value and the new value:
So, we enter the below formula in D2 and copy it down through D7:
=PercentVar(B2, C2)
You see, instead of replicating the percent variance formula in its full form, we just feed a couple of input parameters to LAMBDA and get the same results. Perfect!
Now that you know the basic concept of LAMBDA in Excel, let's discuss a few more formula examples to really get the hang of it.
LAMBDA is ideal for optimizing long difficult-to-understand formulas.
For example, to get number from any position in string, you can utilize this mind-boggling formula:
=IF(SUM(LEN(A2)-LEN(SUBSTITUTE(A2, {"0","1","2","3","4","5","6","7","8","9"}, "")))>0, SUMPRODUCT(MID(0&A2, LARGE(INDEX(ISNUMBER(--MID(A2,ROW(INDIRECT("$1:$"&LEN(A2))),1))* ROW(INDIRECT("$1:$"&LEN(A2))),0), ROW(INDIRECT("$1:$"&LEN(A2))))+1,1) * 10^ROW(INDIRECT("$1:$"&LEN(A2)))/10),"")
The person who can construct it from scratch without copy/pasting deserves a Ph.D. in computer science, agree? :)
Upon a closer look, you may notice that the formula requires just one input value (original string in A2). So, there is nothing that would prevent us from easily converting it into a custom LAMBDA function:
That's it!
=LAMBDA(string, IF(SUM(LEN(string)-LEN(SUBSTITUTE(string, {"0","1","2","3","4","5","6","7","8","9"}, "")))>0, SUMPRODUCT(MID(0&string, LARGE(INDEX(ISNUMBER(--MID(string, ROW(INDIRECT("$1:$"&LEN(string))),1))* ROW(INDIRECT("$1:$"&LEN(string))),0), ROW(INDIRECT("$1:$"&LEN(string))))+1,1) * 10^ROW(INDIRECT("$1:$"&LEN(string)))/10),""))
Once you've made sure that the LAMBDA function works exactly the same as the original formula (for this, use the testing syntax described in the previous section), head over to the Name Manager to define some good name for it, say ExtractNumber.
Voila, you now have a concise and elegant solution to pull numbers from anywhere in a string:
=ExtractNumber(A2)
As mentioned earlier, the LAMBDA function can potentially handle up to 253 parameters. In practice, it would be wise to limit the number of parameters to an absolute minimum because of the following reasons:
For this example, we are going to create a custom function to calculate compound annual growth rate based on this generic CAGR formula:
Which requires 3 input values:
Converted to LAMBDA, the formula looks as follows:
=LAMBDA(BV, EV, N, (EV/BV)^(1/N)-1)
After testing, we go to the Name Manager to name our CAGR function. And here, it makes sense to leave some tips for yourself, and especially for other users of your workbook, explaining what each parameter is. The Comments section is a perfect place for this:
Now, we have our own CAGR function for calculating compound annual growth rate in Excel:
With the beginning value in E2, ending value in E3, and the number of periods in E4, the formula is as simple as this:
=CAGR(E2, E3, E4)
And it works beautifully:
On second thoughts, however, it appears that the N parameter is not actually needed in our case. If all the investments are listed in a column like in the image above, then we can have the number of periods calculated automatically with the help of the ROW function:
In the result, our Lambda becomes a little longer but gets rid of a superfluous argument:
=LAMBDA(BV, EV, (EV/BV)^(1/(ROW(EV) - ROW(BV))) - 1)
And now, you only need to provide the beginning and ending values of the investment:
=CAGR(B2, B7)
Two is better than three, eh? :)
Since the LAMBDA function was designed for Excel 365, which is sometimes called Dynamic Array Excel, these two features wonderfully get along.
To see how it works in practice, let's define a custom function to sort a list by the item count. For this, we'll be using the SORTBY function in conjunction with COUNTIF and UNIQUE.
Assuming the items to be sorted are in C2:C85, the formula takes this shape:
=SORTBY(UNIQUE(C2:C85), COUNTIF(C2:C85, UNIQUE(C2:C85)), -1)
The logic is quite easy to follow:
As the result, our SORTBY formula sorts the list of unique items by the item count and arranges the results from highest to lowest.
With the help of this formula, we get a list of tennis Grand Slam winners in the 21st century sorted by the number of wins. To verify the results, you can return the wins count for each champion by using this formula:
=COUNTIF(C2:C85, E2#)
Having confirmed that the formula works right, we wrap it up in LAMBDA, replacing the range reference with the parameter name (list):
=LAMBDA(list, SORTBY(UNIQUE(list), COUNTIF(list, UNIQUE(list)), -1))
Finally, we name our newly defined function SortByCount, and can now do the same computation with this short and intuitive formula:
=SortByCount(C2:C85)
Like any dynamic array formula, it only needs to be entered in a single cell (E2) and returns multiple values into neighboring cells automatically.
To return a limited number of items, say top 3, top 5, or top 10, you can wrap the SORTBY formula in the INDEX function and use an array constant like {1;2;3} to determine the size of an output array, the 3 most referenced items in our case.
=LAMBDA(list, INDEX(SORTBY(UNIQUE(list), COUNTIF(list, UNIQUE(list)), -1), {1;2;3}))
Our new Lambda function is named TopThree, and it does exactly what its name suggests:
=TopThree(C2:C85)
Like anything defined in Excel's Name Manager, LAMBDA is limited to the workbook it is created in.
Luckily, there is quite an easy way to transfer LAMBDA to another workbook. You simply copy a blank sheet from the old workbook to the new one. As the function was saved in the scope of Workbook, it travels with absolutely any worksheet that you copy or move.
Please note that this method exports absolutely all LAMBDA functions that exist in the original workbook.
Hopefully, these examples have inspired you to look for your own uses of LAMBDA in Excel. For now, let me briefly summarize the key takeaways.
If you are still in doubt if the LAMBDA function is something you really need in your workbooks, here are the three compelling reasons to start using it:
As the LAMBDA function is newly released, it's only natural that it has a few rough edges. Here, we'll mention the most essential drawbacks:
To transfer a LAMBDA-defined function from one workbook to another, you can copy any sheet from the workbook containing the LAMBDA of interest. Then you can delete the copied sheet, but the LAMBDA function will still remain in the Name Manager. Please keep in mind that this method copies all LAMBDAs from the original workbook even if the copied sheet does not contain a single LAMBDA reference.
If you are facing problems while defining a Lambda or your formula throws an error, the following information can help you pin down the cause and fix it.
May occur because of the following reasons:
May be caused by one of these issues:
Occurs because of a circular call of LAMBDA from within itself, e.g. when a recursive Lambda function does not have a way to exit the loop.
May be triggered when creating a new LAMBDA function in a cell without providing input values for testing. To avoid the error, use a special testing syntax described in How to create and test a LAMBDA formula.
When a seemingly simple LAMBDA function you are creating fails, the problem may be in invalid parameter names that can be confused with cell references. In such cases, Excel highlights the parameters like shown in the screenshot below and throws You've entered too few arguments for this function error:
The point is that the strings num1 and num2 match the cell addresses NUM1 and NUM2, and therefore Excel does not accept them as parameter names. Once you change the names, say, to num_1 and num_2, the error disappears, and the LAMBDA function starts working as expected:
The bottom line: if Excel highlights the parameters of a Lambda function you are creating, try different param names that do not look like cell references.
That's how to write and calculate LAMBDA in Excel. In the next article, we'll look at how to create even more powerful recursive functions. Thank you for reading and please stay tuned!
Excel LAMBDA function examples (.xlsx file)
Table of contents