LAMBDA features are new to Microsoft Excel† LAMBDA functions allow you to convert a complex calculation into a simple sheet-level function. You need to know the complex calculation, but they are error-prone and difficult to maintain; for example, if something changes, you could end up changing several sheet-level formulas. Why not use LAMBDA() instead? You enter the complex calculation once, give it a function name, and you’re done.
In this tutorial I explain what a LAMBDA function is and how to use Excel’s new LAMBDA() function. I assume you have at least basic Excel skills. Once you learn how to use LAMBDA functions, expect to use them a lot in your Excel spreadsheets.
SEE: Software Installation Policy (Tech Republic Premium)
I use Microsoft 365 on a Windows 10 64-bit system. Excel’s LAMBDA() function is only available in Microsoft 365 and Excel for the web. I assume you have basic Excel skills. For your convenience, you can: download the demonstration .xlsx file† This article assumes you have basic Excel skills, but you should be able to follow the instructions to success.
What is a LAMBDA() function?
An Excel LAMBDA() function is similar to a user-defined VBA function – without VBA. Basically, Excel’s LAMBDA() allows you to create custom and reusable functions and give them meaningful names using the form:
LAMBDA([parameter1, parameter2, …,] calculation)
where the optional parameter arguments are values you pass to the function—arguments can also refer to a range. The calculation argument is the logic you want to execute. Once that’s all correct, save it all by using Excel’s Name Manager to name it. To use the function, simply enter the function name at the sheet level, just like with all built-in Excel functions.
That’s all nice, but there’s more. Excel LAMBDA() supports arrays as arguments and they can also return results as data types and arrays. The average user may not need that much power, but you should know that it is available.
Before we go any further, there are a few rules to follow when creating a LAMBDA() function:
- LAMBDA() supports 253 parameters, which should be enough for most users.
- LAMBDA() follows Excel’s naming and parameter naming conventions, with only one exception: you cannot use the period (.).
- Like other functions, LAMBDA() will return an error value if applicable.
How to Create a LAMBDA() Function in Excel
Creating a LAMBDA() function in Excel is quite simple. LAMBDA() enters the parameters and calculation arguments using variables. With Excel’s Define Names function, you name the function and enter the LAMBDA() function, and that’s it.
The easiest way to understand what Excel’s LAMBDA() functions can do for you is to start with a simple one. For example, Excel provides a SUM() function but no SUBTRACT() function. You can still subtract, but it’s a simple calculation to get you started:
- Enter the test calculation =B3-C3 in any cell outside the table. If it produces the expected results, move on. If not, keep working on the calculation until it is correct.
- Click the Formulas tab, and then click Define Name.
- In the resulting dialog, type SUBTRACTYL in the name control. The L suffix identifies the function as a LAMBDA() function, but you can use any convention you like.
- For now, don’t change the Scope setting, but you can restrict LAMBDA() to a sheet instead of the workbook.
- Enter a comment describing the LAMBDA() function, such as: Subtracts two numbers†
- In the Refers to control enter LAMBDA() function, =LAMBDA(a,b,ab) †Image A†
- Click OK.
Image A
The variables a and b first identify the values being evaluated and ab is the calculation. Because there are only two variables, SUBTRACTL evaluates only two values or two ranges.
At this point you are ready to use the LAMBDA() SUBTRACTL() function.
How to call an Excel LAMBDA() function
You use LAMBDA() functions the same way you use Excel functions. To demonstrate, enter the function and refer to the values shown in Figure B† = Subtract (B3, C3)† (The period is grammatical and is not part of the function.) B3 and C3 satisfy variables a and b, respectively. The calculation subtracts b from a, in the order specified at the job level.
Figure B
When working with a new LAMBDA() you can check it by entering the original formula. In this case it is =B3-C3. As you can see in Figure C, the control expression and the LAMBDA() return the same results. Even if you checked the calculation before doing SUBTRACTL(), it’s a good idea to test again. If you use a table object (like me), Excel uses structured references, =[@Value1]†[@Value2]†
Figure C
It’s worth noting that you can always pass the values explicitly. For example, the SUBTRACTL function (182.138) returns 44.
Because Excel’s LAMBDA() uses Excel’s formula language, it behaves predictably. For example, if you start entering the function by entering just a few characters, SUBTRACTL() will appear in the AutoComplete list, as shown in Figure D† Note that Excel also displays the description you entered when you named it. The only thing it can’t do yet is show the arguments like a built-in function would.
Figure D
Before looking at a more reasonable and complex LAMBDA(), let’s take a look at a few errors you may encounter when using it.
About Excel LAMBDA() errors
LAMBDA() functions are just as error-prone as built-in functions. You must pass the expected parameters and the calculation logic must be correct. Otherwise you might see errors. Let’s take a look at some of the possibilities:
- #VALUE!: If you see this error value, check your passed arguments – you passed the wrong number.
- #NUM!: Check for a circular reference if you see this error value.
- #NAME!: Check the actual function name you entered for a typo.
For most of us, the #VALUE! Most common and easy to fix. Now let’s look at a more complex LAMBDA().
How to use Excel LAMBDA() to return to top n values
You’ve learned a lot and now it’s time to use what you’ve learned to create a useful LAMBDA(). Calculate the peak n values in a column is a common task and requires a bit of specialized knowledge. In the past, you could use an advanced filter, an expression, or a pivot table. In addition, you can use a conditional formatting rule to highlight those values at the source. The article, How to return the top or bottom n records without a filter or pivot table in Excel uses none of these and relies on Excel’s array functions, SORT() and SEQUENCE().
Now let’s tackle the problem with Excel’s LAMBDA() function. We also use Excel’s SEQUENCE() array function in the form
=LAMBDA(values, n, LARGE(values, SEQUENCE(n)))
Figure E shows the above function in G3. You can tell it’s an array function because the results have a blue border. Let’s break it down so you can see how it works:
- When entering the function, select the Value1 values - do not include the header cell. Doing so satisfies the value argument.
- Enter 3 and satisfy the n argument.
- SEQUENCE(n) is an array function that returns the number of . determines rows to return, but in this case they are numbers from Table1[Value1]†
- The LARGE() function is a built-in Excel function that uses the . returns nthe largest value in a range.
Figure E
The LAMBDA() passes the reference of the numbers you want to evaluate (values) and the number you want the array to return (n). The calculation part, LARGE(values, SEQUENCE(n)) does the job, but the LAMBDA() makes it easy to use. This is one of the great things about LAMBDA() functions: users don’t need specialized knowledge to do their job.
Now that you know how it works, let’s make it:
- Click the Formulas tab, and then click Define Name.
- In the resulting dialog, type TOPnL in the name control. The L suffix identifies the function as a LAMBDA() function, but you can use any convention you like.
- For now, don’t change the Scope setting, but you can limit the LAMBDA() to a sheet.
- Enter a comment describing the LAMBDA() function, such as: Returns the top n values as a LAMBDA()†
- In the Refers to control enter LAMBDA() function, =LAMBDA(values, n, LARGE(values, SEQUENCE(n)))† †Figure F†
- Click OK.
Figure F
After entering TOPnL() in G3 (Figure E), copy it to H3 and see what happens. The reference is relative, so you get a second array of the top three values in Value2, as you can see in Figure G†
Figure G
For more information about returning the top n values, read these TechRepublic articles:
