Introducing the New LAMBDA Function in Excel
It’s fair to say that Excel formulas are becoming like a programming language. Over the past couple of years, Microsoft has fundamentally changed Excel’s calculation engine and introduced a set of functions that allow us to construct formulas to do much more with less.
It doesn’t stop there.
If you are familiar with programming languages, you’ll know what a lambda is. It’s an anonymous function that is passed as an argument or returned from a function call. Well, now that very concept has made its way to Excel.
I am going to introduce to you the new LAMBDA function—which at the time of writing—is only available to Microsoft 365 Office Insiders who are subscribed to the Beta Channel. If you aren’t already, carry out the following steps:
- Go to File, Account and click on the Office Insider dropdown.
- Press Join Office Insider.
- A window will appear allowing you to select Beta Channel from the dropdown.
Whether you have access to this function yet depends on your Beta Channel version. Updates are often rolled out gradually, so it can depend on a few factors. My version is 2102 and the build number is 13729.20000, so it’s worth checking what yours are if the function isn’t showing up.
LAMBDA Basics
LAMBDA allows you to build your own functions—previously only possible to do using VBA. However, given that VBA is gradually becoming obsolete—not to mention the clunky nature of it—being able to do it in a formula has compatibility, convenience, and performance benefits.
The makeup of LAMBDA is:
=LAMBDA(parameter_or_calculation, …Only one argument exists (parameter_or_calculation), but will automatically duplicate each time you add a comma. You don’t need to have any parameters, but you must have at least one calculation for the function to operate.
parameter: name of the value to be passed into the function. Up to 253 parameters are permissible.
calculation: calculation performed that determines how the parameter(s) is used.
Example Workbook
Download from here:
The workbook contains three worksheets of ascending difficulty. For each example, I’ve included the formula along with the output.
Part 1 — Simple Examples
Example 1
This is a very basic but necessary example so you can see how LAMBDA works in practice.
=LAMBDA(x,y,x+y)(5,3)The x and y parameters are stated first; then the x+y calculation takes place. If you neglect the (5,3) part, you end up with a #CALC error. This is because you haven’t passed any values to the parameters. When you do, they must be in the same order you defined them.
Whilst you can use a LAMBDA formula directly in a cell, it’s best for testing purposes. Functions can be centrally stored in Name Manager, although it’s annoying how it only allows for one-line formulas. That’s why I recommend you create your LAMBDAs in the worksheet first, and then copy and paste them into Name Manager.
The Output formula contains the name of the function along with the two parameter values nested inside.
=AddTwoNumbers(5,3)Creating a function name is as simple as going to the Formulas tab, clicking Name Manager and pressing New. The Refers to field must contain only the parameters and calculation (=LAMBDA(x,y,x+y)).
Example 2
To concatenate a list of first and last names, you can use the ampersand character (&) to join parameters and an empty string (" ") to include spaces.
=LAMBDA(FirstName,LastName,FirstName&" "&LastName
)(G17:G21,H17:H21)Notice how the ranges G17:G21,H17:H21 are passed to the parameters. You might not be familiar with spilt ranges, but the advantage of referencing this way is only one formula is required. The output will spill downwards.
Example 3
Similarly to Example 2, concatenation is used to join a list of first and last names, but this time to generate email addresses with a consistent suffix. The LOWER function ensures every character in the string is displayed in lowercase.
=LAMBDA(FirstName,LastName,
LOWER(FirstName&"."&LastName&"@email.com")
)(G27:G31,H27:H31)Part 2—Filter Table Based on User Selection
Example 4
This example is based around a table of the most expensive transfer fees in football (or soccer, for you American folk). It is displayed in descending year order according to when the transfers occurred.
Although a little contrived, you may have faced a scenario where you have a large table you want to filter and sort. Yet, you don’t wish to alter the original table itself, and you don’t want to include all the columns in the output. In this instance, it can be useful to have an additional table that only includes what you want.
The Output table is a filtered and sorted version of the main one, but only includes the transfers that are part of the range selected in the Player column. Furthermore, the list of players is only accompanied by the Fee and Year columns.
Let’s look at the formula behind this:
=LAMBDA(Player,
SORT(CHOOSE({1,2,3},
Player,
INDEX(Table1[Fee],SEQUENCE(ROWS(Player),,XMATCH(INDEX(Player,1),Table1[Player]))),
INDEX(Table1[Year],SEQUENCE(ROWS(Player),,XMATCH(INDEX(Player,1),Table1[Player])))
),
{2,3},-1,-1)
)(I16:I18)The sole parameter is Player, which represents the range selected surrounded by a red border. Even though the blue ranges are visible, you cannot change their references because they are not parameters.
For the calculation argument, a CHOOSE statement is wrapped inside the SORT function and {1,2,3} instructs the creation of three columns.
In those columns, the value1, value2 and value3 arguments house the player, fee and year data, respectively. It’s easy for value1 because the whole function revolves around the Player range selected in Data. However, for value2 and value3, it’s a bit trickier.
The first part of the INDEX statements defines where the final value should come from, so for value2 it’s the Fee column, and for value3 it’s Year.
In the row_num argument, the purpose is to find the top and bottom row of the selected range in the Player column. This is done with the SEQUENCE function, making use of two arguments: rows and start.
ROWS(Player) counts the number of rows selected in the Player range, whilst XMATCH(INDEX(Player,1),TransfersTable[Player]) determines which row to start at. The first player’s name in the range is fed into the lookup_value of the XMATCH function and is searched for in the whole Player column (lookup_array).
INDEX(TransfersTable[Fee/Year],SEQUENCE(ROWS(Player),,
XMATCH(INDEX(Player,1),TransfersTable[Player]))),The sort_index argument dictates which columns should take priority when it comes to sorting. By putting {2,3}, Fee (second column) will be sorted first, and then Year (third column). This is proceeded by sort_order, which determines how these columns should be sorted. In this case it is in descending order for both ({-1,-1}).
Part 3—Recursion
The most exciting thing about LAMBDA formulas is that they are recursive. This means a custom function can be used within itself. Prior to LAMBDA, this could only be done using a For…Next or Do…While VBA loop. Having this capability makes formulas Turing Complete.
Example 5
Sometimes you’ll have some data that has unwanted characters in it, and you need to find a way of cleaning it up. You could use a formula, Find and Replace, or even Text to Columns to strip them out, but these methods aren’t always ideal. This one though allows you to list—in a cell—all the characters you wish to eradicate from a range of data, and the formula will purge each one.
In Data, the top 10 Premier League teams are shown based on the 2019/20 table. The problem is: amidst them is table data that has invaded each team. The LAMBDA formula works its magic and Output displays a cleaned set of teams. Admittedly this example is a bit contrived, but it’s the concept you should focus on!
To understand how recursion works, it’s a good idea to take a look at the Recursion Process table:
The formula cycles through one character at a time on the exclusion list—and evaluates the string for any instances of it. If there are, then they are purged. Once all the characters have been looked at, the looping process finishes.
Let’s look at the formula behind all of this:
=LAMBDA(ClubName,ExcludeCharacters,
IF(ExcludeCharacters="",ClubName,
CleanName(
SUBSTITUTE(ClubName,LEFT(ExcludeCharacters,1),""),
RIGHT(ExcludeCharacters,LEN(ExcludeCharacters)-1))
)
)(G14:G23,$G$11)An IF statement assesses the ExcludeCharacters list to see if it is empty. If it is, then ClubName is returned in its present form. If not, then the function itself (CleanName) is called — this is recursion in a nutshell.
Inside this function is a SUBSTITUTE and RIGHT formula to represent ClubName and ExcludeCharacters respectively. The former targets ClubName and replaces all instances of the first character on the left in ExcludeCharacters with a blank value. Whilst the latter knocks off the first character on the exclusion list with each loop.
Example 6
The final example is based on a set of numbers in a cell, which are added together cumulatively. Although using a recursive LAMBDA is not necessary to achieve this, I thought I would show you a recursive and non-recursive method for your edification.
I’ll start with the non-recursive method:
=LAMBDA(NumberGroup,
SUM(--MID(NumberGroup,SEQUENCE(LEN(NumberGroup)),1))
)(G30)A MID formula is wrapped inside the SUM function, and the goal of this is to increase the start_num value by one and extract the first number (num_chars) from NumberGroup. This is where dynamic arrays are so useful.
Look at what =SEQUENCE(LEN(NumberGroup)) produces on its own:
In the start_num argument, the starting character of NumberGroup incrementally changes by one as part of a sequence, which has been made the same length as the set of numbers. Each number is summed to reach a final figure of 45.
Now let’s look at the recursive method:
=LAMBDA(NumberGroup,AddedNumbers,x,
IF(x=LEN(NumberGroup)+1,
AddedNumbers,
AddNumbersRecursive(NumberGroup,AddedNumbers+MID(NumberGroup,x,1),x+1))
)(G34,0,1)The three parameters are:
NumberGroup: the set of numbers in cell G34.
AddedNumbers: running total of the added numbers.
x: a counter that represents the position of the current number.
The IF statement reads as follows:
If x is equal to the character length of NumberGroup plus 1, then display the value of AddedNumbers. If not, then feed the function back into itself and repeat the same arguments found in its Name Manager entry. This will activate the recursion process in the formula.
Remember, there are three parameters that must go back into the function when it’s called. NumberGroup is the first and it’s static, but AddedNumbers is added to the value of MID(NumberGroup,x,1), which takes one character (1) of NumberGroup and a starting position of x is applied. In the third part, x+1 ensures that the value of x will increase with each number it loops through.
AddNumbersRecursive(NumberGroup,AddedNumbers+MID(NumberGroup,x,1),x+1)With the inner workings out of the way, the final part of the formula is what you specify when you call the AddNumbersRecursive function. You can’t refer to the number group as NumberGroup — it must be a cell reference. This is followed by 0 for AddedNumbers (to start with 0) and 1 for x (to increase by 1) respectively.
=AddNumbersRecursive(G34,0,1)Final Words
Earlier I talked briefly about a gaping problem regarding the storage of functions in Name Manager: you can only have one-line formulas.
I suspect it won’t be long until we do see a much-needed Name Manager update…
“One that I can tell you gets me every time is the experience of editing in the name manager… definitely lots of room for improvement there.”
— Microsoft’s Brian Jones
Microsoft knows the birth of LAMBDA needs to coincide with improvements elsewhere; the Name Manager is the obvious one. As well as a multi-line formula box, there have already been whispers that sharing options will be implemented.
Imagine having an export feature that allows you to save your library of functions. And an import option for loading them into your workbook. These could be used for distributing functions between people.
There’s no doubt that LAMBDA will change how we think about Excel. Our workflows will get better, and productivity will increase as a result. We won’t be spending as much time constructing formulas, as we’ll be reusing our trusty pre-made functions.
LAMBDA may only be at an embryonic stage, but as time progresses, we will all learn new ways to utilise it. There is real potential to unleash possibilities no one ever imagined spreadsheets would be capable of.
It’s time to get LAMBDA-ready.
