Excel 2024: Combining Subformulas into a Single Lambda

Using LAMBDA with Data Types

The team that developed LAMBDA is the same one that created data types and the LET function. That means LAMBDA was designed to work perfectly with Data Types. This enables more complex uses of LAMBDA.

Say that you have city pairs in column A and B. You convert these to Geography data types using the Data Type gallery on the Data tab of the ribbon. The Map icons mean that each cell contains many fields about the city. Two of those fields are Latitude and Longitude.

There's a complicated formula for calculating the distance between two cities using their respective latitude and longitude. You start out assuming the radius of the Earth is 3,959 miles. The formula uses functions for arcsine and cosine and is generally one that most people would never remember: =(3959*2)*ASIN(SQRT(0.5-COS((B2.Latitude-A2.Latitude)*PI()/180)/2+COS(A2.Latitude*PI()/180)*COS(B2.Latitude*PI()/180)*(1-COS((B2.Longitude-A2.Longitude)*PI()/180))/2)).

If you wrap that formula in the LAMBDA function as =LAMBDA(From,To,(3959*2)*ASIN(SQRT(0.5-COS((To.Latitude-From.Latitude)*PI()/180)/2+COS(From.Latitude*PI()/180)*COS(To.Latitude*PI()/180)*(1-COS((To.Longitude-From.Longitude)*PI()/180))/2))) and then store it in the Name Manager with an easy name like MILES, then you can calculate the distance between two cities using a formula as simple as =MILES(A2,B2).

To move that formula from one workbook to another, you simply copy any worksheet from the original workbook to a new workbook, and the names will travel along. With this portability, it's possible to envision a company's accounting department that has a library of custom LAMBDA functions that move from workbook to workbook.

Bonus Tip: Perform a Loop with a Recursive LAMBDA

A LAMBDA function can also call other LAMBDA functions, and it can recursively call itself. This lets you create a LAMBDA function that keeps calling itself until the task is complete.

These are likely the most complicated topic in the whole book. Let me demonstrate how one LAMBDA can solve a problem that would be pretty difficult with regular Excel functions.

I frequently write articles for MrExcel.com. My full stack developer is Suat Ozgur. When Suat is converting my article to a web page, he puts the title in the URL to help with Search Engine Optimization. Converting a title from "Why I say 'Bazinga!' - my #1 Reason for Using =INDEX(MATCH())!!", to a URL requires certain rules. The only characters that are used in our URLs are the lower case letters a through z and the digits 0 through 9 and a dash. As you scan through any article title, there are 37 characters that can be left alone. Another 26 upper case characters need to be converted to lower case. That leaves 192 other characters that have to be replaced with an dash. (Technically, Suat thinks he only has to check the 31 illegal characters that are on a U.S. keyboard. Because he thinks I am too lazy to type a =CHAR(149) to get a bullet point. But Suat doesn't realize that I can press the Alt key and 7 on the number keypad to get bullet points. So he better check all 192 potential bad characters because he never knows when I might surprise him.)

Getting rid of bad characters with SUBSTITUTE would take a long time. To be really safe, you would have to use one SUBSTITUTE function for each of the 192 characters. Since you can not nest more than 64 functions, you would have to break the formula into four columns. Even if you tried to only handle the 31 illegal characters that are on a U.S. keyboard, the problem would be overwhelming.

The process of converting the above phrase to "why-i-say-bazinga-my-1-reason-for-using-index-match-" is called turning the title into a slug. The people who deal with web pages have made word this into a verb, saying that you have to "slugify" next month's article titles.

If you were going to build this in regular Excel formulas, the logic might go like this:

1. Start with character position 1. Store this in a cell column that we will call Index.

2. Convert that character to lowercase using LOWER()

3. See what ASCII code that letter is.

4. If the code for that letter between 48-57 or between 97 to 122? If it is, then use that character. If it is not, then use a hyphen.

5. Take the characters to the left of index, the new character from step 4, and the characters to the right of index. This is the new phrase.

6. Add 1 to the Index.

7. Check to see if the new Index is greater than the number of characters in the title. If it is, then do a final SUBSTITUTE to replace any instances of a double -- with a single hypen. You are done. Stop calculating.

8. But if the Index is not greater than the LEN, go to step 2, this time processing the 2nd character.

It is a crazy worksheet. I managed to convert my article title using just 630 formulas in A3:J65. Here is a tiny fraction of the worksheet.

At the end of the worksheet, the index is finally greater than the LEN, and you finally get an answer in cell J65.

I want us to agree that the above method is pretty ugly and not really reasonable. I don't want to have to explain the formulas that I used above, since we are abandoning that method. However, in case you really want to see the formulas, here they are:

All of the above is ugly. 630 formulas. If the phrase gets longer, you will need more formulas.

It can all be replaced with one LAMBDA function stored in a name called SLUGIFY.

As the author of this book, I am hoping you ignored everything above this. But I really hope you try and understand everything that follows. This is the part that shows you how recursive LAMBDAs work.

Start the function with =LAMBDA and name the two arguments that will be passed to it. The first argument is a phrase. The second argument will always be a 1 and will be stored in a variable called "ndx" because this sounds like "index" but is 40% shorter.

Start off with an IF function that checks to see if we are done. Is the ndx beyond the end of the phrase?.

The part in the box below is the Value_if_True part of the IF statement. If we aren't done with Slugify, then call Slugify again, but make the Index be 1 more than the last time. The inner logic is described after the figure.

These six lines are the part of the formula that build the PHRASE for the next call to Slugify. If you use Excel frequently, all of the functions in this part should feel familiar. Use LOWER and MID to isolate this character. Get the CODE of that character. If the CODE is a letter or digit, then use it, otherwise use a hyphen. The phrase for the next time is going to be the left part, the newly fixed character, and the right part.

Deep inside the LAMBDA is the one line that really does the work. If we have a letter or a digit, keep it. If it is one of the other 192 bad characters, then use a hyphen.

The first time you call Slugify, it will be fixing the first character. The Excel calculation engine will then call Slugify to fix the 2nd character. This keeps going. Eventually, Excel calls Slugify to fix the 64th character, but that's bad because there are only 63 characters in this phrase. So, when ndx is greater than the LEN of the phrase, it is time to finally return the result.

The SUBSTITUTE / TRIM / SUBSTITUTE logic replaces any multiple hyphens with a single hyphen.

Note that up to this point, I've been building the LAMBDA in notepad or in a textbox in Excel.

So, you've written your LAMBDA. You have to give it a name so you can call it. On the Formulas tab, click New Name. Name it Slugify. Paste the formula from the textbox, spaces and all into the Refers To box.

Here is the payoff... you now have a function called SLUGIFY that you can use repeatedly.

The rebel in me (and perhaps in you) wonders what would happen if I changed the 1 in the second argument to 5. What do I get with =Slugify(A2,5)? I tried it. It sort of works. It just does not fix any of the first 4 characters.

Before LAMBDA, I would have created this as a VBA user-defined function. That requires you to understand VBA. And it won't ever work in Excel Online. Now, with LAMBDA, you can simply use familiar Excel logic to build a function that loops through all of the characters in a cell.

Bonus Tip: Branching in a LAMBDA

On an earlier version of the previous LAMBDA, I was getting multiple hyphens in the result. I wondered if you could have a second loop to clean those. Could you do the first task - replacing illegal characters with dash, and then have a second task of looping to look for consecutive hyphens.

Later, I realized that replacing - with space, using TRIM, and then replacing space with hyphen solved the problem without the need for a second loop. However, this formula assigned to the name Slugify.Plus is an example of branching, so I am leaving it in.

The following formula offers two routines. The Branch parameter controls which part of the code is used.

Note that the ndx argument is not used in Branch 2. But since LAMBDA arguments are not optional, you have to put something - I chose 0 - when calling branch 2.

Thanks to Suat Ozgur and Richard Simpson for help with Recursion and Branching.