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.
Featured Course
Black Belt Excel Package
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.
=SortBySum(A4:A19, D4:D19)
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.
=SortBySum(C4:C19, D4:D19)
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.
LAMBDA Syntax
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.
Using the Newly Created LAMBDA
To use the newly created LAMBDA that calculates a discount, select a cell next to the first price and enter the following formula.
=CalcDiscount(A2, 30%)
We have results that are easier to create, control, and modify.
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…
SortBySum(x, y)…is much easier to reproduce by hand compared to…
=SORTBY(UNIQUE(X:X), SUMIFS(Y:Y, X:X, UNIQUE(X:X) ), -1)Continue to PART 2 to see how the recursive MegaReplace function was created and to download the practice workbook.
Leila Gharani
I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.
My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.
