A Recursive LAMBDA function in Excel is useful when you need to create a recursive or iterative solution to a problem.
Recursive functions solve a problem by first solving smaller instances of the same problem. The LAMBDA function enables us to create these recursive functions ourselves.
The rules for using the LAMBDA function in Excel are as follows:
- The function requires a calculation that you want to execute. Users also have the option to add their own parameters.
- The function can then be used to define a custom function that can be called elsewhere.
Let’s take a look at a problem that can be solved using a recursive solution.
Given a particular string, we would like to remove a certain set of characters. For example, we would like to convert the string ‘U. S. A.’ to “USA”, removing the period and space characters.
If we only wanted to remove one character then the solution is trivial. We can simply use the SUBSTITUTE function to replace the character with an empty string. However, since we’ll be removing a set of characters, we will have to call the SUBSTITUTE method multiple times.
A recursive solution to this would be to create a custom Lambda function that cycles through a set of characters to substitute. The function will call itself if there are still more characters to substitute. Once the last character has been handled, the function should return the final iteration with all indicated characters removed.
Now that we know when to use the LAMBDA function, let’s dive into how to write the function itself.
The Anatomy of the LAMBDA Function
The syntax of the LAMBDA function is as follows:
=LAMBDA([parameter1, parameter2, …,] calculation)
Let’s dissect this formula and understand what each of these terms means:
- = the equal sign is how we start any function in Excel.
- LAMBDA() is our
LAMBDAfunction. It is used to create custom and reusable functions. - parameter1 refers to the first value you want to pass to the function.
- parameter2 refers to the second value you want to pass to the function.
- calculation refers to the formula you would like to execute.
- Parameters are optional. Users can enter up to 253 parameters in a
LAMBDAfunction.
A Real Example of Using a Recursive LAMBDA Function in Excel
Let’s take a look at a real example of the LAMBDA function being used in an Excel spreadsheet.
In the table below, we created a custom recursive function to clean text. Users simply need to add the characters to remove in cell D2. The values in column B are obtained using a named function called RemoveChars.
To get the values in Column B, we just need to use the following formula:
=RemoveChars(A2,$D$2)
The RemoveChars function is defined using the LAMBDA function:
=LAMBDA(data,chars, IF(chars<>"", RemoveChars(SUBSTITUTE(data, LEFT(chars, 1), ""), RIGHT(chars, LEN(chars) -1)), data))
How does this function work? Let’s break it down into a few parts.
The core of the function is a SUBSTITUTE formula that tries to find the left-most character in cell D2 in a given string.
With each iteration, the formula will remove the leftmost character from the original value in D2. This will effectively give us a new character to substitute at each iteration. We can use the RIGHT and LEN functions to do this.
Converting the core formula into a LAMBDA function requires us to generalize our formulas. Instead of specific cell references, we’ll use parameter names. The data parameter will refer to the string of text to remove characters from. The chars parameter will refer to the set of characters to remove.
This will give us the following formula:
= RemoveChars(SUBSTITUTE(data, LEFT(chars, 1), ""), RIGHT(chars, LEN(chars) -1))
Finally, we need to structure the LAMBDA function to use the RemoveChars function recursively.
All recursive functions must have a point of exit or a condition that stops the recursion. In this case, we want the formula to stop removing characters once the chars parameter is reduced to an empty string.
While the chars parameter is not an empty string yet, the formula should proceed with the recursion.
This is why the final formula has a structure like this:
=LAMBDA(data, chars, IF(chars="", data, RemoveChars(…)))
You can make your own copy of the spreadsheet above using the link attached below.
If you’re ready to try out the LAMBDA function in Excel, let’s begin writing it ourselves!
How to Use the LAMBDA Function in Excel to Perform a Recursive Solution
This section will guide you through each step needed to start using recursive LAMBDA functions in Excel. You’ll learn how we can use a recursive implementation of the SUBSTITUTE function to remove an arbitrary set of unwanted characters.
Follow these steps. to start using the LAMBDA function:
- First, look for the Name Manager tool under the Formulas tab. The Name Manager will allow us to define our custom Lambda function.
- In the Name Manager dialog box, click on the New button to create a new Name.
- Under the Name input box, enter your desired function name. Set the Scope to ‘Workbook’. Finally, enter the desired lambda function into the Refers to input box.
- Hit the OK button to add the new function into the Name Manager. The function should now appear in the dialog box.
- Users can now type the formula into the formula bar. Excel even recognizes the function as you are typing it.
- You can now use the custom formula in your spreadsheet. In this example, we’ve also filled in our RemoveChars formula with the parameters indicated in the
LAMBDAformula.
- Fill in the rest of the column with the custom
LAMBDAfunction by dragging down the first instance we made of the formula.
That’s all you need to remember to start using the LAMBDA function in Excel. This step-by-step guide shows how you can easily create your own custom recursive solution that you can call anywhere in your sheet.
You can now use the LAMBDA functions in Excel together with the various other Excel formulas available to create powerful and efficient spreadsheets.
Are you interested in learning more about what Microsoft Excel can do? Stay notified of new guides like this by subscribing to our newsletter!
