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.
Featured Course
Power Excel Bundle
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.
Working Recursively
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 = "", TIf “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, MegaReplaceYou 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.
OFFSET(B,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.
Since LAMBDA has been released into the wild, many developers began testing the function by pushing its abilities to the limit.
Microsoft hasn’t been idle either. Creating (and reusing) LAMBDAs has since become easier with the Advanced Formula Environment.
More bigger, better, and simpler things are possibly still in store.
Only time will tell.
Practice Workbook
Feel free to Download the Workbook HERE.
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.
