LAMBDA is Excel’s New Super Function

Contents

At the moment, Microsoft Excel has almost five hundred worksheet functions available through the Function Wizard window – button fx in the formula bar. This is a very decent set, but, nevertheless, almost every user sooner or later encounters a situation where this list does not contain the function he needs – simply because it is not in Excel.

Until now, the only way to solve this problem was macros, i.e. writing your own user-defined function (UDF = User Defined Function) in Visual Basic, which requires appropriate programming skills and is, at times, not easy at all. However, with the latest Office 365 updates, the situation has changed for the better – a special “wrapper” function has been added to Excel LAMBDA. With its help, the task of creating your own functions is now solved easily and beautifully.

Let’s look at the principle of its use in the following example.

As you most likely know, Excel has several date parsing functions that allow you to determine the number of the day, month, week, and year for a given date. But for some reason there is no function that determines the number of the quarter, which is also often needed, right? Let’s fix this shortcoming and create with LAMBDA own new function to solve this problem.

Step 1. Write the formula

Let’s start with the fact that manually in the usual way we will write a formula in a sheet cell that calculates what we need. In the case of the quarter number, this can be done, for example, like this:

Step 2. Wrapping up in LAMBDA and testing

Now it’s time to apply the new LAMBDA function and wrap our formula in it. The function syntax is as follows:

=LAMBDA(Variable1; Variable2; … VariableN ; Expression)

where the names of one or more variables are listed first, and the last argument is always a formula or a calculated expression that uses them. Variable names should not look like cell addresses and should not contain dots.

In our case, there will be only one variable – the date for which we calculate the quarter number. Let’s call the variable for it, say, d. Then wrapping our formula in a function LAMBDA and substituting the address of the original cell A2 with a fictitious variable name, we get:

Please note that after such a transformation, our formula (in fact, correct!) began to produce an error, because now the original date from cell A2 is not transferred to it. For testing and confidence, you can pass arguments to it by adding them after the function LAMBDA in parentheses:

Step 3. Create a name

Now for the easy and fun part. We open Name Manager tab formula (Formulas — Name Manager) and create a new name with the button Create (Create). Come up with and enter a name for our future function (for example, Nomkvartala), and in the field Link (Reference) carefully copy from the formula bar and paste our function LAMBDA, only without the last argument (A2):

Everything. After clicking on OK the created function can be used in any cell on any sheet of this workbook:

Use in other books

Because created with LAMBDA Since user-defined functions are, in fact, named ranges, you can easily make them available not only in the current workbook. It will be enough to copy the cell with the function and paste it anywhere in the sheet of another file.

LAMBDA and dynamic arrays

Custom functions created with a function LAMBDA successfully support work with new dynamic arrays and their functions (FILTER, UNIK, GRADE) added to Microsoft Excel in 2020.

Let’s say we want to create a new user-defined function that would compare two lists and return the difference between them – those elements from the first list that are not in the second. Life’s work, isn’t it? Previously, for this they used either functions a la VPR (VLOOKUP), or PivotTables, or Power Query queries. Now you can do with one formula:

In the English version it will be:

=LAMBDA(a;b;ФИЛЬТР(a;СЧЁТЕСЛИ(b;a)=0))(A1:A6;C1:C10)

Here the function COUNTIF counts the number of occurrences of each element of the first list in the second, and then the function FILTER selects only those of them who did not have these occurrences. By wrapping this structure in LAMBDA and creating a named range based on it with a name, for example, SEARCH DISTRIBUTION – we will get a convenient function that returns the result of comparing two lists in the form of a dynamic array:

If the source data is not ordinary, but “smart” tables, our function will also cope without problems:

Another example is dynamically splitting text by converting it to XML and then parsing it cell by cell using the FILTER.XML function we recently parsed. In order not to reproduce this complex formula manually every time, it will be easier to wrap it in LAMBDA and create a dynamic range based on it, i.e. a new compact and convenient function, naming it, for example, RAZDTEXT:

The first argument of this function will be the cell with the source text, and the second – the separator character, and it will return the result in the form of a horizontal dynamic array. The function code will be as follows:

=LAMBDA(t;d; TRANSPOSE(FILTER.XML(““&SUBSTITUTE(t;d? “«)&»“;”//Y”)))

The list of examples is endless – in any situation where you often have to enter the same long and cumbersome formula, the LAMBDA function will make life noticeably easier.

Recursive enumeration of characters

All previous examples have shown only one, the most obvious, side of the LAMBDA function – its use as a “wrapper” for wrapping long formulas in it and simplifying their input. In fact, LAMBDA has another, much deeper, side that turns it into almost a full-fledged programming language.

The fact is that a fundamentally important feature of LAMBDA functions is the ability to implement them in recursion – logic of calculations, when in the process of calculation the function calls itself. From the habit, it may sound creepy, but in programming, recursion is a common thing. Even in macros in Visual Basic, you can implement it, and now, as you can see, it has come to Excel. Let’s try to understand this technique with a practical example.

Suppose we want to create a user-defined function that would remove all given characters from the source text. The usefulness of such a function, I think, you do not need to prove – it would be very convenient to clear littered input data with its help, right?

However, compared to the previous, non-recursive examples, two difficulties await us.

We will have to come up with a name for our function before we start writing its code, because in it, this name will already be used to call the function itself.
Entering such a recursive function into a cell and debugging it by specifying arguments in brackets after LAMBDA (as we did earlier) will not work. You will have to create a function immediately “from scratch” in Name Manager (Name Manager).

Let’s call our function, say, CLEAN and we would like it to have two arguments – the text to be cleaned and the list of excluded characters as a text string:

Let’s create, as we did earlier, on the tab formula в Name manager named range, name it CLEAR and enter in the field Range following construction:

=LAMBDA(t;d;IF(d=””;t;CLEAR(SUBSTITUTE(t;LEFT(d);””);MID(d;2;255))))

Here the variable t is the original text to be cleared, and d is the list of characters to be deleted.

It all works like this:

Iteration 1

The fragment SUBSTITUTE(t;LEFT(d);””), as you might guess, replaces the first character from the left character from the set d to be deleted in the source text t with an empty text string, i.e. removes the “A”. As an intermediate result, we get:

Vsh zkz n 125 rubles.

Iteration 2

Then the function calls itself and as input (the first argument) receives what is left after cleaning in the previous step, and the second argument is the string of excluded characters starting not from the first, but from the second character, i.e. “BVGDEEGZIKLMNOPRSTUFHTSCHSHSHCHYYYYYA.,” without the initial “A” – this is done by the MID function. As before, the function takes the first character from the left of the remaining ones (B) and replaces it in the text given to it (Zkz n 125 rubles) with an empty string – we get as an intermediate result:

125 ru.

Iteration 3

The function calls itself again, receiving as the first argument what is left of the text to be cleared at the previous iteration (Bsh zkz n 125 ru.), And as the second argument, the set of excluded characters truncated by one more character to the left, i.e. “VGDEEGZIKLMNOPRSTUFHTSCHSHSHCHYYYYUYA.,” without the initial “B”. Then it again takes the first character from the left (B) from this set and removes it from the text – we get:

sh zkz n 125 ru.

And so on – I hope you get the idea. With each iteration, the list of characters to be removed will be truncated on the left, and we will search for and replace the next character from the set with a void.

When all the characters run out, we will need to exit the loop – this role is just performed by the function IF (IF), in which our design is wrapped. If there are no characters left to delete (d=””), then the function should no longer call itself, but should simply return the text to be cleared (variable t) in its final form.

Recursive iteration of cells

Similarly, you can implement a recursive enumeration of cells in a given range. Suppose we want to create a lambda function named REPLACEMENT LIST for wholesale replacement of fragments in the source text according to a given reference list. The result should look like this:

Those. at our function REPLACEMENT LIST there will be three arguments:

cell with text to process (source address)
the first cell of a column with values to search from the lookup
the first cell of the column with replacement values from the lookup

The function should go from top to bottom in the directory and replace sequentially all options from the left column To find to the corresponding options from the right column Substitute. You can implement this with the following recursive lambda function:

Here, the variable t stores the original text from the next column cell Address, and the variables n and z point to the first cells in the columns To find и Substitute, respectively.

As in the previous example, this function first replaces the original text with the function SUBSTITUTE (SUBSTITUTE) data on the first line of the directory (i.e. SPbon St. Petersburg), and then calls itself-itself, but with a shift in the directory down to the next line (i.e. replaces St. Petersburg on St. Petersburg). Then calls itself again with a shift down – and replaces the already Peter on St. Petersburg etc.

Shift down at each iteration is implemented by a standard excel function DISPOSAL (OFFSET), which in this case has three arguments – the original range, row shift (1) and column shift (0).

Well, as soon as we reach the end of the directory (n = “”), we must end the recursion – we stop calling ourselves and display what has accumulated after all the replacements in the source text variable t.

That’s all. No tricky macros or Power Query queries – the whole task is solved by one function.

How to use Excel’s new dynamic array functions: FILTER, SORT, UNIC
Replacing and cleaning up text with the SUBSTITUTE function
Creating macros and user-defined functions (UDFs) in VBA