How to Create Your Own Reusable Functions with Advanced LAMBDA Functions in Excel

Excel’s LAMBDA function is an advanced function that creates custom reusable functions without VBA codes. Excel introduced the LAMBDA function in their recently updated versions. In this article, we will show how to create your own reusable functions with advanced LAMBDA functions in Excel for complex tasks.

What is the LAMBDA Function?

The LAMBDA function in Excel creates custom functions based on any Excel formula/calculations to reuse throughout the Excel workbook. It allows users to give task-related names for the respective custom functions. LAMBDA replaces many tasks that typically require VBA coding.

To create the LAMBDA function, you just need to specify the parameter and the existing formula for calculations to return the expected result. LAMBDA can wrap up complex formulas into one function. LAMBDA function is available in Excel 365 and Excel 2021.

Basic Syntax:

=LAMBDA(parameter_or_calculation,...)

• parameter: Input values or references to pass to the function, it can be a string or number. You can enter up to 253 parameters.
• calculation: The formula that returns a result based on the parameters of the function. It must be the last argument. This argument is required.

Creating Reusable LAMBDA Function in Excel

Let’s consider a sales dataset where you may perform some calculations, but Excel doesn’t offer a direct function or formula. You can create your own custom functions by using the LAMBDA function to perform specific sales-related calculations.

Simple LAMBDA Function

Let’s create a simple LAMBDA function to calculate the achievement percentage for each salesperson.

The logic of the achievement score will be sales divided by target values.

Formula:

=LAMBDA(sales, target, (sales / target))

Here,

• sales: First input value for reference; parameter1.
• target: 2nd input value for reference; parameter2.
• (sales / target): It is the calculation that returns a result based on the parameters

Inert the following LAMBDA function into a cell:

=LAMBDA(sales,target, (sales / target))(C2, D2)

This formula will return the achievement score. To convert it into a percentage >> select Percentage from Numbers.

Make the Function Reusable

• Go to the Formulas tab >> select Name Manager.
• Click New, and define.

In the Edit Name box;

• Name: AchievementScore
• Insert the following formula in Refers to: box and click OK.

=LAMBDA(sales, target, (sales / target))

To use the reusable AchievementScore LAMBDA function in Excel, insert the following formula in cell E2.

Formula:

=AchievementScore(B2, C2)

You can drag the formula to calculate the achievement percentages for all salespersons.

Creating Advanced Reusable LAMBDA Functions

Example 1: Determine Sales Status

Create a function to determine the salesperson’s status regarding target sales whether a salesperson achieved their target or not. You can use the IF formula within the LAMBA function.

Save and Use:

• Go to the Formulas tab >> select Define Name.
•  Set Name as TargetStatus.
• Insert the following formula in the Refers to box and click OK.

=LAMBDA(sales, target, IF(sales >= target, "Achieved", "Not Achieved"))

Insert the following formula in the selected cell H2 in Excel.

=TargetStatus(B2, C2)

This formula will compare the sales and target values to return the sales target status of the salesperson.

Example 2: Calculate Regional Total Sales

You can create a LAMBDA function to calculate total sales by region using the FILTER function to filter sales values of specific regions.

Formula:

=LAMBDA(region,salesRange,regionRange, SUM(FILTER(salesRange, regionRange=region, 0)))

• Save it in the Name Manager as RegionTotalSales.
• Insert the following formula in cell I2 to get the total sales of the North and I3 for the South region:

=RegionalSales("North",C2:C6,B2:B6)

=RegionalSales("South",C2:C6,B2:B6)

This formula will return total sales based on the region name defined in the formula.

Example 3: Calculate Weighted Average Sales Performance

To analyze sales performance across different regions you can calculate the weighted averages.

Formula:

=LAMBDA(values, weights, SUM(values * weights) / SUM(weights))

• Save it in Name Manager as WeightedAverageSales.
• Insert the following formula in cell J2.

=WeightedAverageSales(C2:C6,E2:E6)

This formula multiplies each sales value by its corresponding weight, sums them up, and divides by the total of the weights to return the weighted average.

Using LAMBDA with Dynamic Arrays

You can apply LAMBDA functions across entire ranges or arrays without using array formulas, with the help of dynamic arrays.

Let’s create a function that applies a 10% commission to all values in a given array.

=LAMBDA(array, array * 0.1)

• Save it in Name Manager as SalesCommission.
• Insert the following formula in cell K2.

=SalesComission(C2:C6)

This formula will return the commission amount based on the sales values of C2:C6.

Creating a Reusable LAMBDA with Multiple Steps

You can also break down complex calculations into multiple steps using the LET function within the LAMBDA function.

Let’s create a function to identify the salesperson with the highest sales in each region.

=LAMBDA(region, salesRange, regionRange, nameRange,
LET(
regionalSales, FILTER(salesRange, regionRange = region),
topSales, MAX(regionalSales),
topPerformer, INDEX(nameRange, MATCH(topSales, salesRange, 0)),
topPerformer
)
)

In this formula, it takes input (region, salesRange, regionRange, nameRange) and applies a structured logic to return the top sales performer dynamically based on the selected region.

LET function creates temporary variables to simplify and optimize calculations.

• regionalSales: Filters sales data by the specified region.
• topSales: Identifies the highest sales value in that filtered data.
• topPerformer: Matches the highest sales to the corresponding salesperson.

These steps are executed efficiently within a single function, improving readability and performance.

• Save the function as RegionalTopSaler in Name Manager.
•  Insert the following formula in the selected cell:

=RegionalTopSaler("North", C2:C6, B2:B6, A2:A6)

The formula will return the name of the top performer in the North region.

=RegionalTopSaler("South", C2:C6, B2:B6, A2:A6)

This formula will return the name of the top seller in the South region.

Conclusion

By using Excel’s LAMBDA function, you can create powerful, reusable functions to simplify your workflows and calculations. You can use advanced techniques like LET, to handle multiple parameters, and incorporate error handling, to create complex functions for your specific needs without VBA code. This function improves productivity and reduces the complexity of existing formulas. You can practice these reusable custom functions and unlock a whole new level of functionality in Excel.