How to use the LAMBDA function

What is the LAMBDA function?

The LAMBDA function lets you build custom functions using only regular Excel functions, no VBA is needed. These custom functions based on the LAMBDA function are available only in your workbook.

The Name Manager lets you create a unique easy to remember name for your custom LAMBDA function that allows you to reuse the complex formula across worksheets.

What makes the LAMBDA function even more powerful is its ability to create recursive formulas.

1. Introduction

What is a custom function in this regard?

Section 3 below demonstrates how to create a custom function using the LAMBDA function that converts Kelvin to Fahrenheit. It is then named KtoF in the "Name Manager" which allows the user to pass values to the custom function like this: =KtoF(B4) where B4 is a cell reference to cell B4. The use of custom functions makes it easier to reuse complex formulas across worksheets. By creating a custom function, you can encapsulate a complex calculation or series of steps into a single, reusable custom function. This allows you to apply the same logic repeatedly without having to retype or copy-paste the entire formula each time.

What is VBA?

VBA stands for Visual Basic for Applications and is a programming language that is integrated into Microsoft Excel and other Office applications. It allows users to automate tasks, create custom functions, and build sophisticated applications within the Excel environment.

1. Automating repetitive tasks, such as data entry, formatting, or report generation.
2. Creating macros to record and replay a series of actions for beginners.
3. Developing user-defined functions that can be used in Excel formulas. Extending the built-in functionality of Excel.
4. Building custom dialog boxes, forms, and user interfaces to interact with data and perform specific tasks.
5. Performing complex data analysis, manipulation, and transformation tasks. Automating data import, export, and processing workflows.
6. Generating dynamic reports, charts, and dashboards. Automating the creation and distribution of reports.
7. Creating standalone Excel-based applications with custom features and functionality.Developing add-ins or plugins that extend the capabilities of Excel.

This all sounds amazing, however, there are a few downsides as well. Sharing VBA-based solutions with users who don't have the necessary permissions or software can be challenging. VBA code can potentially be used to run malicious scripts or macros, posing a security risk if the code is not properly vetted and secured. Some organizations may have strict policies or restrictions on the use of VBA in Excel due to security concerns. This is where LAMBDA function may be used when VBA is not available because of security policies.

Why is the LAMBDA custom function only available in my workbook?

You can export a LAMBDA function to other workbooks, here is how:

Copy a Blank Sheet: One straightforward approach is to copy a blank sheet from the workbook containing the LAMBDA functions to the new workbook. This method transfers all the LAMBDA functions along with the sheet.

What is a recursive Excel formula?

Recursive Excel formulas refer to formulas that reference themselves within their own calculation. In other words, a recursive formula calls itself as part of its own evaluation.

2. LAMBDA Function Syntax

LAMBDA([parameter1, parameter2, …,] calculation)

Back to top

3. Example 1

This example demonstrates how to build a custom LAMBDA function, there are three main steps.

1. Create a formula.
2. Build the LAMBDA function.
3. Name the LAMBDA function.

We are going to build a custom LAMBDA function that converts Kelvin to Fahrenheit, it is a simple calculation and works great as a demonstration.

3.1 Create a formula

This example shows how to convert Kelvin to Fahrenheit.

This example shows how to convert Kelvin to Fahrenheit, which can be useful when working with temperature measurements in different scientific or engineering contexts. Being able to convert between these scales allows for better collaboration, data sharing, and understanding across disciplines. This conversion is particularly useful when working with temperature data that originates from sources using the Kelvin scale, such as in physics, chemistry, or astronomy, and needs to be expressed in the more commonly used Fahrenheit scale.

The math formula is: F = 1.8(K - 273.15) + 32

F - Fahrenheit which is a temperature scale named after the German physicist Daniel Gabriel Fahrenheit.
K - Kelvin which is the base unit of temperature in the International System of Units (SI).

Formula in cell C4:

Explaining formula

Step 1 - Subtract Kelvin with 273

The minus operator lets you subtract numbers in an Excel formula.

B4-273

0-273 equals -273

Step 2 - Multiply by 1.8

The asterisk character lets you multiply numbers in an Excel formula. The parentheses allow you to control the order of operation.

1.8*(B4-273)

becomes

1.8*-273 equals -491.4

Step 3 - Add 32

The plus operator lets you add numbers in an Excel formula.

1.8*(B4-273)+32

becomes

-491.4 + 32 equals -459.4

Back to top

3.2 Build the LAMBDA function

The LAMBDA function has the following syntax: LAMBDA([parameter1, parameter2, …,] calculation)

Formula in cell D4:

The (B4) at the end of the formula is the argument being passed to the custom function. So, when you call this formula, it will take the value in cell B4, plug it into the custom function as the K argument, and return the corresponding temperature in Fahrenheit.

Explaining formula

Step 1 - Formula

This is the formula we built in section 3.1 There is only one input value in this formula.

1.8*(B4-273)+32

Step 2 - LAMBDA function

The LAMBDA function has up to 253 parameters, however, we need only one parameter, in this example named K.

LAMBDA([parameter1, parameter2, …,] calculation)

LAMBDA(K, 1.8*(K-273)+32)

The bolded part above is the formula we built in section 3.1, cell reference B4 is replaced with parameter K.

Step 3 - Pass a value to the LAMBDA function

The LAMBDA function uses parentheses to pass values to the LAMBDA formula.

LAMBDA(K,1.8*(K-273)+32)(B4)

The parameters must be in the same order as you specified them, in this case, it doesn't matter. There is only one parameter.

3.3 Name the LAMBDA function

Excel feature "Named ranges" allows us to assign a custom name to the LAMBDA function. I named it KtoF, shown in cell E4 in the image above.

Formula in cell E4:

Here is how to assign a name to a LAMBDA function:

1. Go to tab "Formulas" on the ribbon.
2. Press with left mouse button on "Name Manager"  button. A dialog box appears.
   
3. Press with left mouse button on "New.." button. Another dialog box appears.
   
4. Type a name.
5. Paste the LAMBDA function to "Refers to:" field.
   
6. Press with left mouse button on the "OK" button.
   
7. Press with left mouse button on the "Close" button.

The function we created appears if we type the first characters in the function name in a cell.

4. Analyze word frequency in a cell range

This article demonstrates two ways to calculate the number of times each word appears in a given range of cells. Excel 365 has many new functions that are really useful, I am using the REDUCE function combined with the LAMBDA and VSTACK functions here.

You can also use the TEXTJOIN and the TEXTSPLIT functions, however, they have a limit of 32 767 characters which the REDUCE function doesn't have.

This article also shows a user defined function (UDF) that will work for most Excel versions.

4.1. How to calculate word frequency in a given cell range - Excel 365 LAMBDA function

Excel 365 formula in cell E3:

Formula in cell F3:

The formula in cell F3 is explained here: Count how many times a string exists in a cell range (case insensitive)

Explaining the formula in cell E3

Step 1 - Split string based on a space character

The TEXTSPLIT function splits a string into an array based on delimiting values.

Function syntax: TEXTSPLIT(Input_Text, col_delimiter, [row_delimiter], [Ignore_Empty])

TEXTSPLIT(x, " ")

Step 2 - Stack arrays vertically

The VSTACK function combines cell ranges or arrays. Joins data to the first blank cell at the bottom of a cell range or array (vertical stacking)

Function syntax: VSTACK(array1,[array2],...)

VSTACK(TEXTSPLIT(x, " "),TEXTSPLIT(y, " "))

Step 3 - Build the LAMBDA function

The LAMBDA function build custom functions without VBA, macros or javascript.

Function syntax: LAMBDA([parameter1, parameter2, …,] calculation)

LAMBDA(x,y,VSTACK(TEXTSPLIT(x, " "),TEXTSPLIT(y, " ")))

Step 4 - Iterate through each cell in cell range B3:C12

The REDUCE function shrinks an array to an accumulated value, a LAMBDA function is needed to properly accumulate each value in order to return a total.

Function syntax: REDUCE([initial_value], array, lambda(accumulator, value))

REDUCE(,B3:C12,LAMBDA(x,y,VSTACK(TEXTSPLIT(x, " "),TEXTSPLIT(y, " "))))

Step 5 - Replace errors with -

The IFERROR function if the value argument returns an error, the value_if_error argument is used. If the value argument does NOT return an error, the IFERROR function returns the value argument.

Function syntax: IFERROR(value, value_if_error)

IFERROR(REDUCE(,B3:C12,LAMBDA(x,y,VSTACK(TEXTSPLIT(x, " "),TEXTSPLIT(y, " ")))),"-")

Step 6 - Rearrange array to a single column layout

The TOCOL function rearranges values in 2D cell ranges to a single column.

Function syntax: TOCOL(array, [ignore], [scan_by_col])

TOCOL(IFERROR(REDUCE(,B3:C12,LAMBDA(x,y,VSTACK(TEXTSPLIT(x, " "),TEXTSPLIT(y, " ")))),"-"))

Step 7 - Extract unique distinct values from the array

The UNIQUE function returns a unique or unique distinct list.

Function syntax: UNIQUE(array,[by_col],[exactly_once])

UNIQUE(TOCOL(IFERROR(REDUCE(,B3:C12,LAMBDA(x,y,VSTACK(TEXTSPLIT(x, " "),TEXTSPLIT(y, " ")))),"-")))

Back to top

4.2. How to calculate word frequency in a given cell range - UDF

This user defined function creates a unique distinct list of words and how many times they occur in the selected range.

4.2.1 User defined Function Syntax

FREQWORDS(cell_range, position)

4.2.2 Arguments

4.2.3 Example

Array formula in cell E3:E30:

Array formula in cell F3:F30:

How to create an array formula

1. 1. Select cell range E3:E30.
   2. Copy (Ctrl + c) array formula and paste (Ctrl + v)  to formula bar.
      
   3. Press and hold Ctrl + Shift simultaneously.
   4. Press Enter once.
   5. Release all keys.

4.2.4 VBA

'Name function and declare arguments
Function FreqWords(tbl_array As Range, pos As Integer) As Variant()

'Declare variables and their data types
Dim cell As Variant, wrds As Variant, i As Integer
Dim a As Integer, j As Integer
Dim tmp() As String, nr() As Integer

'Redimension variable tmp so it can grow using Redim Preserve
ReDim tmp(0), nr(0)

'Assign 1 to first value in array variable nr
nr(0) = 1

'Iterate through cells in cell range
For Each cell In tbl_array

  'Split words in cell
  wrds = Split(cell)

  'Iterate thorugh words
  For i = 0 To UBound(wrds)

    'Iterate through arrayvariable tmp 
    For j = 0 To UBound(tmp)

      'If variable wrds equal variable tmp then increase value in variable nr by 1
      If wrds(i) = tmp(j) Then
        nr(j) = nr(j) + 1
        a = 1
        Exit For
      End If
    Next j

    'Check if a is not equal to 1
    If a <> 1 Then
      'Copy value from variable wrds to tmp
      tmp(UBound(tmp)) = wrds(i)

      'Add another container to array variable tmp
      ReDim Preserve tmp(UBound(tmp) + 1)
      ReDim Preserve nr(UBound(tmp))
      nr(UBound(tmp)) = 1
    End If
    a = 0
  Next i
Next cell

'Return values in column 1 if argument pos is equal to 1
If pos = 1 Then
  ReDim Preserve tmp(UBound(tmp) - 1)
  FreqWords = Application.Transpose(tmp)
Else

  'Return values in column 2 if argument pos is not equal to 1
  ReDim Preserve nr(UBound(nr) - 1)
  FreqWords = Application.Transpose(nr)
End If
End Function

4.2.5 How to add the User defined Function to your workbook

1. Press Alt-F11 to open visual basic editor
2. Press with left mouse button on Module on the Insert menu
3. Copy and paste vba code
4. Exit visual basic editor

Back to top

Get the Excel file

Frequent-words.xls

Back to top

5. Multiply numbers in each row by entire cell range

This section demonstrates a recursive LAMBDA function and a User Defined Function (UDF) that multiplies numbers in each row with the remaining rows in a cell range.

The image above demonstrates the UDF in cell range B5:D8, it takes the first row and multiplies the numbers with all rows in the same cell range. It then continues to the second row and multiplies that row with all rows in the cell range.

5.1. Multiply cells - recursive LAMBDA function

The image above shows the result of a recursive LAMBDA formula in cell B6 that multiplies each row by all rows in the given cell range, read the question here.

A cell range containing 2 rows returns 4 rows. A cell range with 3 rows returns 9 rows, and so on.

Excel 365 formula in cell B6:

Recursive LAMBDA formula:

5.1.1 How to create a named range

1. Go to the tab "Formulas" on the ribbon.
2. Press the left mouse button on "Name manager". A dialog box appears.
   
3. Press the left mouse button on the "New..." button, see the image above.
   
4. Name the formula "Multiply".
5. Copy/Paste the formula to the "Referes to:" field.
   
6. Press the left mouse button on the "OK" button.

5.1.2 Explaining recursive lambda formula

LAMBDA(rng,n,result,IF(n=(ROWS(rng)+1),DROP(result,1),Multiply(rng,n+1,VSTACK(result,rng*INDEX(rng,n,0)))))

Step 1 - Get row by row from cell range rng

The INDEX function returns a value or reference from a cell range or array, you specify which value based on a row and column number.

Function syntax: INDEX(array, [row_num], [column_num])

INDEX(rng,n,0)

becomes

INDEX({1,2,3;4,5,6},1,0)

and returns

{1,2,3} for the first row.

Step 2 - Multiply rng by the row

The asterisk lets you multiply numbers in an Excel formula.

rng*INDEX(rng,n,0)

becomes

{1,2,3;4,5,6}*{1,2,3}

equals

{1,4,9;4,10,18}.

The next iteration becomes

{1,2,3;4,5,6}*{4,5,6}

and returns

{4,10,18;16,25,36}

Step 3 - Add calculation to result

The VSTACK function combines cell ranges or arrays. Joins data to the first blank cell at the bottom of a cell range or array (vertical stacking)

Function syntax: VSTACK(array1,[array2],...)

VSTACK(result,rng*INDEX(rng,n,0))

becomes

VSTACK("",{1,4,9;4,10,18})

and returns

{"",#N/A,#N/A;1,4,9;4,10,18}.

The next iteration becomes

VSTACK({"",#N/A,#N/A;1,4,9;4,10,18},{4,10,18;16,25,36})

and returns

{"",#N/A,#N/A;1,4,9;4,10,18;4,10,18;16,25,36}

Step 4 - Count rows in range rng

The ROWS function calculate the number of rows in a cell range.

Function syntax: ROWS(array)

ROWS(rng)+1

returns 3.

Step 5 - Delete first row in array

The DROP function removes a given number of rows or columns from a 2D cell range or array.

Function syntax: DROP(array, rows, [columns])

DROP(result,1)

becomes

DROP({"",#N/A,#N/A;1,4,9;4,10,18;4,10,18;16,25,36},1)

and returns

{1,4,9;4,10,18;4,10,18;16,25,36}

Step 6 - Return the result except the first row if n = rows in range rng

The IF function returns one value if the logical test is TRUE and another value if the logical test is FALSE.

Function syntax: IF(logical_test, [value_if_true], [value_if_false])

IF(n=(ROWS(rng)+1),DROP(result,1),Multiply(rng,n+1,VSTACK(result,rng*INDEX(rng,n,0))))

Step 7 - Build LAMBDA function

The LAMBDA function build custom functions without VBA, macros or javascript.

Function syntax: LAMBDA([parameter1, parameter2, …,] calculation)

LAMBDA(rng,n,result,IF(n=(ROWS(rng)+1),DROP(result,1),Multiply(rng,n+1,VSTACK(result,rng*INDEX(rng,n,0)))))

5.1.3 Get Excel *.xlsx file

Get the Excel file

Multiply-rows.xlsx

5.2. MultiplyCells - User Defined Function

Below is a User Defined function I made, it multiplies each row by all other rows in the specified range.

The picture above shows you the UDF using values in cell range B2:B3 and returns the calculated values to cell range B5:D8.

This is a UDF (custom function), you need to copy code to the code module before you can use it.

UDF syntax

Arguments

There is only one argument in this UDF.

range - Cell range

Back to top

5.2.2. VBA Code

'Name User Defined Function and dimension parameters
Function MultiplyCells(rng As Range)

'Dimension variables and declare data types
Dim rng1 As Variant
Dim tbl() As Variant
Dim rr As Single, r As Single, c As Single, tr As Single

'Save values in range object rng to array variable rng1
rng1 = rng.Value

'Redimension array variable tbl based on rows and columns in range object rng
ReDim tbl(1 To rng.Cells.Rows.CountLarge ^ 2, 1 To rng.Cells.Columns.CountLarge)
tr = 1

'For ... Next statements
For rr = LBound(rng1, 1) To UBound(rng1, 1)
For r = LBound(rng1, 1) To UBound(rng1, 1)
For c = LBound(rng1, 2) To UBound(rng1, 2)

'Multiply values and save the product to array variable tbl
tbl(tr, c) = rng1(rr, c) * rng1(r, c)
Next c

'Add 1 to variable tr and save to variable tr
tr = tr + 1
Next r
Next rr

'Return numbers in array variable tbl to worksheet
MultiplyCells = tbl
End Function

Back to top

5.2.3. How to enter the UDF as an array formula

Enter this UDF as an array formula, if the range has 2 rows and 3 columns enter the UDF in a cell range with 4 (2*2) rows and 3 columns.

Here are the steps to enter this UDF as an array formula:

1. Select cell range B5:D8.
2. Type the UDF name and argument:
   =MultiplyCells(B2:D3)
3. Press and hold CTRL + SHIFT simultaneously.
4. Press Enter once.
5. Release all keys.

If you did it right the formula now has a curly bracket before and after. Like this {=MultiplyCells(B2:D3)}. Don't enter these yourself. If you are unsure, watch the formula bar carefully in the animated picture above.

Back to top

5.2.4. Where to put the code?

1. Press Alt+ F11 to open the Visual Basic Editor (VBE).
2. Press with mouse on "Insert" on the top menu, see the image above.
3. Press with mouse on "Module" to insert a module to your workbook.
4. Copy VBA code above.
5. Paste to code window, see the image above.
6. Exit VBE and return to Excel.

Copy the following code to a module. How to insert a module to a workbook.

Back to top

5.2.5. Explaining VBA code

The code explained here is located in the code module. You can find the code module in the VB Editor, press Alt+F11 to open the VB Editor. Press with left mouse button on "Module" found in project explorer.

Function name and arguments

A User Defined Function always starts with "Function" and then a name. This UDF has only one argument. Variable rng is a range object, read more about Defining data types.

Function MultiplyCells(rng As Range)

Create Custom Functions

Declaring variables

tbl() is an array and a variant variable, tbl has two parentheses meaning it is an array. rng1  is also a variant.  rr, r, c, and tr are Single variables. Read more about Defining data types.

Dim rng1 As Variant
Dim tbl() As Variant
Dim rr As Single, r As Single, c As Single, tr As Single

Dim statement

Save values from rng (range object) to rng1 (variant)

This speeds up the function considerably if you are working with large cell ranges. Excel copies all the values from the sheet and puts them in memory (array).

rng1 = rng.Value

Build array

ReDim dimensions the tbl array variant, it has the number of rows of the range argument with the power of 2. The values in the array are numbered from 1 to n. There are as many columns as in the range argument.

ReDimtbl(1 To rng.Cells.Rows.CountLarge ^ 2, 1 Torng.Cells.Columns.CountLarge)

ReDim Statement

Use variables r and c to save values in array

Variable tr keeps track of where to save the next column values in tbl.

tr = 1

For ... Next statement

Repeats a group of statements a specified number of times, here we want to multiply each row by all rows.

For rr = LBound(rng1, 1) To UBound(rng1, 1)
For r = LBound(rng1, 1) To UBound(rng1, 1)
For c = LBound(rng1, 2) To UBound(rng1, 2)

For ... Next statement

Save value to tbl array

The variables tr, c, rr, and r help us keep track of which values to use and where to save.

tbl(tr, c) = rng1(rr, c) * rng1(r, c)

Add 1 to variable tr

tr = tr + 1

Return tbl values to function

MultiplyCells = tbl

End udf

A function procedure ends with this statement.

End Function

Back to top

Get the Excel file

Multiply-cells-efficientlyv2.xlsm

Back to top

'LAMBDA' function examples

5 easy ways to VLOOKUP and return multiple values
This post explains how to lookup a value and return multiple values. No array formula required.

Calculate machine utilization
Question: I need to calculate how many hours a machine is utilized in a company with a night and day […]

Convert date ranges into dates
This article demonstrates how to create a list of dates based on multiple date ranges. Table of contents Convert date […]

Functions in 'Logical' category

The LAMBDA function function is one of 16 functions in the 'Logical' category.

Excel categories

Comments (28)