Excel LAMBDA Function Explained (with Recursive LAMBDAs)
Imagine using an Excel function that creates other functions.
There is such a function, and its name is LAMBDA.
The LAMBDA function is currently available to Office 365 BETA Channel subscribers only, and it’s limited to only a fraction of those users.
The LAMBDA function is targeted at beginning Excel users as well as Excel Experts. It’s a function that everyone can benefit from.
Understanding the LAMBDA function is important. Although you may not create your own custom functions using LAMBDA, you are likely to encounter LAMBDA functions created by others. It’s a good idea to have a basic understanding of this amazing function. This way, you can speak about LAMBDA functions with authority at your next Excel party. (You know those parties are amazing!)
The most impactful aspect of the LAMBDA function is that it can be used recursively. This means that the function can call itself as part of its logic.
Let’s dive right in and see how it all works.
To give you a taste of the power of LAMBDA, consider the following example.
We have asked users to list their software skills, but the way they have listed these skills is not the way we wish to have them appear in the report. We want to locate specific words/phrases and replace them with corrected items from a list.
Entries like “Office 365” should be replaced with “Microsoft 365”; “powerbi” should become “Power BI”, etc.
If you examine the sample data closely, you will see that several of the user entries will require multiple corrections.
We need to take each item in each of the “Skills” column and loop through the list of “Before” entries, and if it exists, replace it with the equivalent “After” entry.
This process, if performed using VBA and macros, would be accomplished using a “For…Next” or “Do…While” loop.
We will accomplish the same thing with a formula.
Enter the Amazing LAMBDA function!
We’re going to tell this story in reverse.
I have already created the solution, but first I want to show you how it’s used so you can see how beneficial it can be.
I select a cell next to the first entry in the list and enter the following formula.
=MegaReplace([@Skills], $F$3, $G$3)
The results are shown below.
Understanding the Custom Function
The custom function is named “MegaReplace” and has three arguments:
- [@Skills] – This is the cell to be examined. In this case, because we are examining a proper Excel Table, we use a Structured References of [@Skills]. If this had been a plain table, we would have made a traditional cell reference of C3.
- $F$3 – This points to the start of the list of words to search for and replace if they exist.
- $G$3 – This points to the start of the list of words to act as replacement words.
This is an example of a Recursive LAMBDA function. We’ll see how this is performed towards the end of the post. For now, let’s learn about LAMBDA with an easier example.
LAMBDA Function – Practical Example
Suppose in your day-to-day workflow you are tasked with creating a list of items that are sorted by the sum of Salary in Descending order.
Our data looks like the following.
We need two lists: one that aggregates all Salary values by Department; another that aggregates all Salary values by Position. Each list must be presented in Descending order.
We are not presenting the aggregations of Salary, only the list of corresponding Department/Position.
If we were to tackle this problem using older functions, we could use the SUMIF or SUMIFS function to aggregate by category. We could then sort by Salary, then delete the aggregations.
Using a LAMBDA function, we can craft a custom function named “SortBySum” that takes two arguments: the column sort and the column to aggregate.
The result is a list of departments presented in descending order by salary.
Recycling LAMBDA Logic
Because the SortBySum function was created using a LAMBDA function, I can reuse the function on any set of data with a similar structure.
To create a Descending list of Positions by Salary, enter the following formula.
Here is our sorted list of positions.
I’ll show you the logic behind this example in a bit. First, we need to understand how the LAMBDA function works and see it in action using something simple.
Creating a LAMBDA Function
LAMBDAs are just like any other function, except for the fact that you create them.
The LAMBDA function has the following syntax:
=LAMBDA( [parameter1, parameter2, …], calculation)
You can list up to 253 separate parameters for use in the calculation. Understand that the last argument (calculation) is ALWAYS seen as the logic; all previous arguments are parameters.
All parameters are optional, although you will likely use at least one parameter. The calculation is required.
A LAMBDA operates as a 3-step process:
- Accepts an input or series of inputs
- Processes those inputs according to some defined logic
- Return a result
Let’s look at an example of a VERY simple LAMBDA, one that is so simple, you would likely do it the old-fashioned way.
By keeping the logic simple, we can focus on the mechanics of the LAMBDA creation process and not worry so much about the logic of the operation being performed.
We have a list of prices and we wish to calculate a 30% discount for each price. We click next to the first price and enter the following formula.
=LAMBDA(Price, Price * (1 – 30%) )
NOTE: You can use ANY names you want for the parameters (ex: a, b, c, 1, 2, 3, price, tax, age, etc.) I have elected to call the cell being calculated “Price”.
When we press ENTER, we don’t exactly get what we were expecting.
Why did we receive an error? Simple; we created the logic of a LAMBDA but have not supplied any content for it to work with. It’s like a car with no fuel.
An Interesting Feature of LAMBDA
An interesting thing that can be done when creating a LAMBDA is to supply a test scenario during the LAMBDA creation process. This helps us know if our LAMBDA has been created and operating properly.
By adding open parentheses to the end of the formula then providing an example of the needed parameter(s), we can see if everything is functioning as expected.
=LAMBDA(Price, Price * (1 – 30%) ) (A2)
The LAMBDA function accepts the temporary data in cell A2, stores the data in the variable named “Price”, then uses the contents of “Price” wherever it is needed in the logic.
Filling the formula down to the adjacent cells, we see the discounted prices.
LAMBDA with Multiple Parameters
If we wanted to make this formula more dynamic, say by allowing the formula to accept a percentage for the discount instead of a hard-coded 30%, we could write the LAMBDA as follows.
=LAMBDA(Price, Discount, Price * (1 – Discount) )
Below is a test using a hard-coded 30%. This could just as easily be a cell reference that holds the 30% value.
=LAMBDA(Price, Discount, Price * (1 – Discount) ) (A2, 30%)
“Why would I do this?”
If you’re wondering why someone would make something that appears more complicated than necessary, you’re about to see the payoff.
If we take the entire LAMBDA function (without the additional test at the end) and assign it a name, we are now able to use that name with a simple list of parameters in our daily workflow.
Assigning a Name to a LAMBDA
To assign a name to a LAMBDA function, perform the following steps:
- If you have an existing LAMBDA function in a test cell, highlight and copy the formula (less any test arguments at the end). If you do not have an existing LAMBDA, proceed to Step 2.
- Open the Name Manager by selecting Formulas (tab ) -> Defined Names (Group) -> Name Manager.
- In the Name Manager dialog box, click NEW and supply a name, such as “CalcDiscount” along with the LAMBDA formula.
NOTE: It is recommended that comments be added to explain the purpose of each parameter.
Returning to the Practical Example from Earlier
Remember the example from earlier where we listed Departments and Positions in descending order by Salary?
To produce the finished LAMBDA, we follow a simple 3-step process:
- Construct the logic in a regular cell as if we were performing this task “the old way”.
- Convert the formula from Step 1 into a LAMBDA by assigning parameter names to cell references/static values.
- Using the Name Manager, assign a name to the LAMBDA create in Step 2.
The LAMBDA behind that “SortBySalary” formula is listed below.
Step 1 – Create the Test Formula
Select an empty cell and write the following formula.
=SORTBY(UNIQUE(C4:C19), SUMIFS(D4:D19, C4:C19, UNIQUE(C4:C19) ), -1)
The result is below.
Step 2 – Convert the Formula to a LAMBDA
The next step is to replace each cell reference with a parameter name. We will use the following parameter names:
- Items = cells C4:C19
- Salary = cell D4:D19
The LAMBDA formula is as follows.
=LAMBDA(Items, Salary, SORTBY(UNIQUE(Items), SUMIFS(Salary, Items, UNIQUE(Items) ), -1) )
Step 3 – Assign a Name to the LAMBDA
The final step is to highlight the entire LAMBDA formula from Step 2 and copy it for use in the Name Manager.
As before, we open the Name Manager and create a new name with the following settings.
Using Our Newfound Power
With our LAMBDA created, we can use it on any table where we need to sort words in a unique, descending order by a different column.
This yields the following list of Divisions in Descending order by Revenue.
Repeated use of…
…is much easier to reproduce by hand compared to…
=SORTBY(UNIQUE(X:X), SUMIFS(Y:Y, X:X, UNIQUE(X:X) ), -1)
Recursive LAMBDA Functions
A recursive LAMBDA involves a process that is looped; that keeps repeating.
Our setup from the start of this post involved looping through a list of “Before” words to see if any of those worlds exist within a set of words in a cell. If those “Before” words exist, we replace those words with “After” words.
We saw that the finished LAMBDA function looks like the following.
=MegaReplace([@Skills], $F$3, $G$3)
[@Skills] is the cell we wish to scan for word replacements, F3 is the start of the list of “Before” words, and G3 is the start of the list of “After” words.
The result is like magic.
The way this works is that the LAMBDA calls itself as many times as is needed depending on the length of the “Before/After” list.
This is known as a Recursive Function.
If you were to try this type of trickery with a traditional Excel function, you would be presented with a rather angry “Circular Reference” message that sends most users into a panic.
This can be overcome provided you have a point of exit. In other words, a rule that allows your LAMBDA to exit the LAMBDA.
Writing a recursive LAMBDA can be challenging because they are difficult to test and debug when they don’t work correctly.
This is like a “Chicken & the Egg” scenario. You must create both successfully at the same time for the recursive LAMBDA to work.
Let me show you my way of building recursive LAMBDAs.
Recursive LAMBDA Creation
As with the earlier LAMBDA creation process, the first step is to create a core formula in a traditional Excel cell that mimics the ultimate behavior of the LAMBDA logic.
It helps to think about this one cell at a time.
We will start small and examine a single cell (C3) to see if it contains the word listed in cell F3. If it does, we will replace it with the word in cell G3.
An ideal function for this action is the SUBSTITUTE function.
The SUBSTITUTE function has three arguments: the text we are searching (cell C3), the text we are searching for (cell F3), and the text we are replacing it with if it exists (cell G3).
=SUBSTITUTE(C3, F3, G3)
The result is the original text because the “Before” word does not exist in cell C3.
Because we need to loop through each entry in the “Before” list when examining the contents of cell C3, we create a second SUBSTITUTE function that uses the output of the first SUBSTITUTE function to search for the next item in the “Before” list.
=SUBSTITUTE(F13, F4, G4)
Because the output of the first SUBSTITUTE contains the “Before” text “Power Point”, it is replaced with the “After” text “PowerPoint”.
Creating a third iteration that uses the output of the second SUBSTITUTE and the next row of text in the “Before/After” list produces the following.
Creating a fourth iteration that uses the output of the third SUBSTITUTE and the next row of text in the “Before/After” list produces a correction to the “Office 365” entry, replacing it with “Microsoft 365”.
This is the essence of Recursion. We make the core formula loop through the list.
Once we have determined the core formula (in this case, SUBSTITUTE), the next thing to determine is the number of parameters needed to feed the core formula. In this case, we have three cell references, so we will need three parameters.
The final thing to consider is establishing an exit strategy.
How do we inform the recursion when we have exhausted the list of “Before/After” words?
The exit strategy depends on what you are doing, and the data set you are doing it upon.
For our example, we can tell the recursion to exit when it encounters blank cells in the “Before/After” table. This indicates the completion of looping through the list of replacement words.
IMPORTANT: If you don’t plan for an exit, your function will not work correctly.
Writing the LAMBDA for Recursion
As we require three parameters for the three cell references used by the SUBSTITUTE function, we will use the following:
- T = the text being examined
- B = the “Before” text
- A = the “After” text
We will select an empty cell and write the following start to the LAMBDA formula.
=LAMBDA(T, B, A,
The next part is to test to see if the “Before” cell being examined is empty. This will be performed using an IF function. We test to see if the “B” parameter is empty, then we exit the LAMBDA by outputting the current state of “T”.
IF(B = “”, T
If “B” is not empty, then we need to invoke recursion.
This is where things get tricky.
As we will ultimately name this LAMBDA “MegaReplace”, we will make a call to “MegaReplace” as the FALSE action of the IF function.
IF(B = “”, T, MegaReplace
You must be able to imagine the result; you can’t really test it here.
Because the “MegaReplace” function requires three parameters, the first parameter will be the SUBSTITUTE formula created earlier.
SUBSTITUTE(T, B, A)
This will act as our “T” in the “MegaReplace” function.
Next, the “B” in the “MegaReplace” must loop through the list of “Before” entries. This can be accomplished using an OFFSET function.
Remember, LAMBDAs don’t use cell references. We use parameters. The cell references are in the parameters. The “B” refers to the current “Before” cell location.
Continuing with the OFFSET function:
OFFSET(B, 1, 0)
This moves 1 row down and 0 columns left/right.
The LAMBDA is now looking as follows.
=LAMBDA(T, B, A, IF(B = “”, T, MegaReplace(SUBSTITUTE(T, B, A), OFFSET(B, 1, 0)
If we move down 1 row in the “Before” column, we must also move down 1 row in the “After” column. We need to perform another OFFSET with nearly the same logic, except we use “A” instead of “B”.
OFFSET(A, 1, 0)
The revised formula now looks like so.
=LAMBDA(T, B, A, IF(B = “”, T, MegaReplace(SUBSTITUTE(T, B, A), OFFSET(B, 1, 0), OFFSET(A, 1, 0)
The final steps are to complete all the parentheticals by placing the perfect number of closed parentheses at the end of the formula.
=LAMBDA(T, B, A, IF(B = “”, T, MegaReplace(SUBSTITUTE(T, B, A), OFFSET(B, 1, 0), OFFSET(A, 1, 0) ) ) )
When we press ENTER we receive a #CALC! error. This is because the formula is calling a “MegaReplace” function that doesn’t yet exist.
This takes us to the third and final step, the naming of the LAMBDA.
Naming the Recursive LAMBDA
To assign a name to the LAMBDA created in the previous step, highlight and copy the entire LAMBDA formula. Next, open the Name Manager and create a new Named Range called “MegaReplace” using the LAMBDA formula in the Refers to field.
PRO TIP: If you attempt to use the left and right arrow keys to navigate through the pasted formula in the Refers to field, you are likely to become VERY frustrated at the behavior. To mitigate this behavior, press the F2 key before pressing the arrow keys. Pressing F2 switches Excel from ENTER mode to EDIT mode allowing cursor movement without disrupting the formula.
The BIG Moment
It’s now time to put our hard work to the test.
We return to the cell next to our first entry in the data to write our “MegaReplace” formula.
Because the data is stored in a proper Excel Table, the “MegaReplace” formula is propagated down the remaining rows in the table.
Because the entire formula is dynamic, if we change or add an item to the Skills that should be replaced, the “MegaReplace” formula springs into action and presents the corrected version of the data.
Closing Thoughts on the LAMBDA Function
For me, these are super-exciting developments. Microsoft is continually pushing the boundaries of what we can do with Excel.
No longer will users be required to become semi-fluent in writing VBA code to create custom functions.
What would have taken months to become proficient enough to use practically can now be achieved in hours.
As we don’t know when LAMBDA will be released into the wild or the many ways developers will test LAMBDA by pushing its abilities to the limit, we may witness something bigger, better, and possibly simpler in the final release.
Only time will tell.
Feel free to Download the Workbook HERE.
Learn Excel from a Microsoft MVP
Check out my bestselling Excel Courses
Learn anytime that fits your schedule.
Download files. Practice. Apply.