LAMBDA functions in Excel are recursive! This means they can call themselves. One classic example of recursion is computing the factorial of a number. For example, 4 factorial, written 4! is computed as 4 * 3 * 2 * 1, which equals 24. 5! = 5 * 4 * 3 * 2 * 1 = 120. Also, 1! = 1 and by definition, 0! = 1. If you are a programmer you know how to write loops. Recursion is like that. It loops back on itself and exits when a certain condition is met.

Excel already has a factorial function called FACT. However, for illustration of recursion, we’ll create a LAMBDA function called Factorial. it is =LAMBDA(n,IF(n<2,1,n*Factorial(n-1))). We’ve called our function Factorial, but we can call it what we like.

The above factorial example is a fairly simple one. We don’t need a factorial LAMBDA function because Excel comes with the FACT function. A more advanced example is next. It’s based on a YouTube video.

Learn with YouTube

Here’s an “advanced” video on recursive LAMBDA. Leila creates a function that does a search and replace for many search terms on an entire range all at once. It’s also dynamic. This would be helpful in the data-cleaning process in the Data Analytics Life Cycle. You might have a column of data that has numerous typos or spelling mistakes. You can create another list of “before” and “after” that the LAMBDA function will go through and replace the text for each one. Excel RECURSIVE Lambda – Create loops with ZERO coding!. This video creates a recursive LAMBDA function called MegaReplace that does a series of search and replace operations with the SUBSTITUTE function. To understand how this works you will also need to know how OFFSET works.