Excel's LAMBDA Functions: An easy guide to creating custom functions

Before Excel introduced the LAMBDA function, creating a custom function requires VBA or macro. Now, with LAMBDA, you can easily implement it using the familiar Excel syntax. This guide will guide you step by step how to use the LAMBDA function.

It is recommended that you read the parts of this guide in order, first understand the grammar and simple examples, and then learn practical applications.

The LAMBDA function is available for Microsoft 365 (Windows and Mac), Excel 2024 (Windows and Mac), and Excel for the web. Excel 2019 and earlier does not support this feature.

LAMBDA function syntax

Creating a custom function using LAMBDA requires two parameters:

 <code>=LAMBDA(x, y)</code>

in:

• x is the input variable (up to 253),
• y is the calculation formula.

The input variable ( x ) cannot conflict with cell references, nor can it contain periods; the calculation formula ( y ) is always the last parameter of the LAMBDA function.

Simple example

Enter the following formula in Sheet1 cell A1 in a blank Excel workbook:

 <code>=LAMBDA(a,b,a*b)</code>

(Do not press Enter for the time being)

a,b defines variables, and a*b is the calculation formula. If a is 4 and b is 6, the result is 24.

After pressing Enter, a #CALC! error appears because the value has not been assigned yet.

You can test it by adding variable values ??at the end of the formula:

 <code>=LAMBDA(a,b,a*b)(4,6)</code>

After pressing Enter, the cell will display 24.

Although it is easier to enter directly =4*6 , the advantage of LAMBDA is that it can name calculation formulas and reuse them, especially when the calculations are complex. When modifying a formula, you only need to modify the function itself to affect all relevant calculations.

Double-click the cell, select the original LAMBDA formula (the content before the first bracket), and press Ctrl C to copy.

Press the Esc key and click Define Name in the Formula tab.

In the New Name dialog box:

Click OK. Enter =SIMPLELAMBDA(9,6) in cell A1 and press Enter, and the result is 54.

Cell references can also be used, for example =SIMPLELAMBDA(A1,A2) .

Practical application examples

Suppose you need to create a LAMBDA function that adds 20% VAT in the UK to all costs.

First, enter =B2*1.2 in the first cell.

Double-click the cell and add the LAMBDA function:

 <code>=LAMBDA(cost,cost*1.2)(B2)</code>

Copy the LAMBDA formula, click "Define Name", and name it AddVAT .

Delete column C data and enter =AddVAT(B2) in cell C2.

If VAT is reduced to 15%, just modify "1.2" in the function definition to "1.15".

All calculations using the AddVAT function are automatically updated.

Notes when using LAMBDA functions

• The LAMBDA function can only be used in the workbook where it was created.
• The function name must be unique.
• The input variable name cannot conflict with the cell reference, nor can it contain periods, and the number of variables cannot exceed 253.

Excel's LAMBDA function can combine any existing function to perform complex calculations, making it more powerful. Once you master it, you can try more complex calculations.

The above is the detailed content of How to Use LAMBDA in Excel to Create Your Own Functions. For more information, please follow other related articles on the PHP Chinese website!