Lambda function in Excel: complete guide and practical examples

Informatec Digital » Resources » Lambda function in Excel: complete guide and practical examples

The Lambda function in Excel has revolutionized how we work with formulas in Microsoft spreadsheets. It allows you to create your own custom functions using only Excel's formula language, without touching a single line of VBA or traditional programming . It's as if you have the ability to add new, custom-designed native functions to the program.

Furthermore, a number of associated functions have emerged around LAMBDA, such as BYROW, BYCOL, MAP, SCAN, REDUCE, and MAKEARRAY , designed to work with ranges and matrices in a much more flexible and powerful way. These functions behave, broadly speaking, like small loops that iterate through the data and apply a transformation defined by LAMBDA, opening up a vast array of possibilities for advanced analysis directly on the spreadsheet.

What is the LAMBDA function in Excel and what is it used for?

The Lambda function is a tool that allows you to define custom functions using only Excel formulas. Instead of programming in VBA or relying on macros, you can encapsulate any complex calculation in a single, reusable function, with its own parameters and a clear, clean final result.

In practice, LAMBDA turns any formula into a function that you can reuse as many times as you want. Its main advantage is that it integrates seamlessly with the rest of Excel's calculation engine and can be combined with standard functions, references, defined names, and dynamic arrays.

The basic syntax when used directly in a cell is:

=LAMBDA(parameter1; parameter2; …; parameterN; calculation)(value1; value2; …; valueN)

In this structure, parameter1, parameter2, …, parameterN are the names you give to the variables within the function, while calculation is the formula that uses those parameters to generate a result. Finally, within the second set of parentheses, you pass the actual values ​​that those parameters will take when the function is executed.

If you use Excel's Name Manager to create a permanent LAMBDA function, the syntax changes slightly, because you define the function there, but you don't call it yet. In that case, the format would be:

=LAMBDA(var1; var2; …; varN; calculation)

Later you would call that function using the name you gave it in the Name Manager, simply typing the name and arguments just as you would with SUM, AVERAGE, or any other standard function.

Best practices when creating and testing Lambda functions

When you start working with Lambda functions, it's important to follow a few guidelines to ensure they behave as expected and you don't waste time debugging complicated errors. One of the most practical ways to begin is to create and test the Lambda function directly in a cell.

The usual procedure is to first write the complete formula with the definition of LAMBDA and the call in the same expression, so you can immediately see if the result is as expected. This way, you can detect syntax or logic errors before saving it as a named function.

For example, a very typical test structure would be:

=LAMBDA(); calculation)(test_values)

To check something very simple like adding 1 to a number, you could use:

=LAMBDA(number; number + 1)(1)

In this case, the function would return the value 2. It's a very simple example, but it serves to illustrate the mechanics: first you define the parameters and the calculation, and then you call that function passing the corresponding argument.

A key recommendation to avoid the #CALC! error is to ensure your LAMBDA function always returns a result . This is achieved by clearly including an expression at the end that produces a single value or an array, depending on your needs. If you see the #CALC! error during testing, check that the formula is actually generating something Excel can display as a result.

Once you have tested the LAMBDA function in a cell and see that it works correctly, it's a good time to move that logic to the Name Manager and turn it into a reusable custom function across the entire sheet or workbook.

Relationship of LAMBDA with the new matrix functions

Around LAMBDA, a series of advanced functions have emerged, such as BYROW, BYCOL, MAP, SCAN, REDUCE and MAKEARRAY (the latter translated in some versions as ARCHIVOMAKEARRAY), which rely on LAMBDA to apply transformations to ranges and complete matrices.

The general idea is that these functions iterate through ranges of data (by rows, by columns, or element by element) and, for each element or group of elements, execute a Lambda function that you define. In other words, they work like loops, but integrated into Excel's formula language.

This allows you to perform operations that previously required auxiliary columns, intermediate tables, or even macros, directly with a single array formula that expands and returns results for the entire range at once.

Among the functions related to LAMBDA, REDUCE, MAP, SCAN, BYCOL, BYROW, and MAKEARRAY stand out , each with a specific objective: traversing rows, applying transformations by columns, accumulating results, creating matrices calculated from scratch, etc. They all have in common that they use LAMBDA as an internal "engine," to which they pass values ​​and accumulators as they move through the matrix.

BYROW function: iterate through rows and return results row by row

The BYROW function is used to apply a LAMBDA function to each row in a range and return an array with one value for each row processed. It's a very efficient way to calculate subtotals or statistics row by row without having to copy formulas vertically.

Its general syntax is:

=BYROW(matrix; LAMBDA(row; expression))

The first argument is the array or range you want to iterate through (for example, B2:D7), and the second is a LAMBDA function that takes each row in that range as a parameter, one by one. The LAMBDA function returns the value you want to associate with that row (possibly a sum, an average, a logical check, etc.).

Imagine you have a data table in the range B2:D7 and you want to get a subtotal for each row. You could write something like this in cell E2:

=BYROW(B2:D7; LAMBDA(row; SUM(row)))

The result would be an output vector with one value for each row of the matrix B2:D7, where each value represents the sum of the elements in that row. This way, you don't need to write SUM row by row: BYROW does it for you and spills the result down.

BYCOL function: apply LAMBDA by columns

Very similar to BYROW, the BYCOL function is designed to iterate through an array by columns instead of rows. It applies a LAMBDA function to each column in the range and returns an array of results where each element corresponds to a column.

Its typical syntax is:

=BYCOL(array; LAMBDA(column; expression))

In this case, the parameter received by LAMBDA is the entire column of the matrix being processed at each step. Similar to BYROW, the function returns a vector, but this one is designed to work with totals or indicators by column.

Continuing with the previous example, if you want to calculate the average of each column in the range B2:D7 , you could place a formula like this in cell B8:

=BYCOL(B2:D7; LAMBDA(column; AVERAGE(column)))

The result will be a matrix with the same number of columns as B2:D7, where each position contains the average of that column . This way you get all the averages in one fell swoop without having to drag and drop formulas or worry about relative references.

MAKEARRAY function (MAKEARRAYFILE): create computed arrays

The MAKEARRAY function (sometimes shown as ARCHIVOMAKEARRAY) allows you to generate a completely new array by specifying the number of rows and columns, and calculating each element using a LAMBDA function. It does not start from an existing range, but builds the array from scratch.

Its general syntax is:

=MAKEARRAY(rows; columns; LAMBDA(row; column; expression))

The `rows` argument indicates how many rows the output matrix will have, `columns` defines the number of columns, and the LAMBDA function receives the row and column indices being calculated in each iteration as parameters. With this information, you can construct virtually any numerical or text pattern.

A very illustrative example is creating a matrix where each element indicates its own position. In any cell, you could write something like:

=MAKEARRAYFILE(3; 2; LAMBDA(row; col; -(row & col)))

The result would be a 3 row by 2 column matrix , where each value represents a combination of row and column (for example, 11, 12, 21, 22, 31, 32), transformed according to the calculation you enter (in this case, the negative sign applied to the row&column concatenation).

Another interesting use of MAKEARRAY is converting a vector into an array while controlling how many elements you take. Suppose you want to create an array with the first 6 values ​​of a vertical range. You could first create an array of positions with MAKEARRAYFILE, get the k smallest position values, and finally use INDEX to retrieve the actual elements from the original range.

An example of a formula, combining several functions, could have this structure:

=LET(arrPos; MAKEARRAYFILE(3; 2; LAMBDA(row; col; -(row & col))); arrPosF; MATCH(arrPos; LeastK(arrPos; SEQUENCE(6))); INDEX(G8:G13; arrPosF))

Here, LET is used to define intermediate names (arrPos, arrPosF), an array of positions is constructed with ARCHIVOMAKEARRAY (3×2), the 6 smallest positions are selected with SMALLEST and SEQUENCE, and finally INDEX is used to return the corresponding values ​​from the range G8:G13. It is a powerful example of how to combine LAMBDA and dynamic array functions to perform complex transformations without macros.

MAP function: element-by-element transformation

The MAP function is used to iterate through one or more arrays simultaneously and return a new array where each output element is calculated by applying a LAMBDA function to the corresponding input element(s). It is equivalent to a classic "map" function in functional programming.

The basic syntax is:

=MAP(matrix1; LAMBDA_or_more_matrices)

In its simplest form, it takes a single array and a LAMBDA function that receives each value from that array. This LAMBDA function transforms the value and returns the new version that will form part of the output array, maintaining the same dimensions as the original array.

For example, if you want to iterate through a vertical range A21:A26 and leave the original number if it's even or a hyphen if it's odd, you could use something like:

=MAP($A$21:$A$26; LAMBDA(param1; IF(ES.PAR(param1); param1; «-«)))

In this case, MAP analyzes each element of A21:A26. LAMBDA checks with IS.EVEN if the number is even. If it is, it returns the number itself; otherwise, it returns a dash. The result is an array of the same size as the original range, but with the transformation applied to each element.

This approach is very useful when you want to apply conditional logic, text conversion, value normalization, or any other simple operation, avoiding auxiliary columns and repetitive formulas.

SCAN function: cumulative and intermediate results

The SCAN function is used to examine an array by applying a LAMBDA function to each value and generating an output array showing all the intermediate values ​​of the accumulation process. It is very similar to REDUCE, but instead of returning only the final result, it preserves each step.

Its general syntax is:

=SCAN(; array; LAMBDA(accumulator; value))

The first argument, which is optional, is the initial value of the accumulator (for example, 0 if you are adding). The second argument is the array or range you want to iterate through. Finally, the LAMBDA function receives two parameters: the accumulator (the partial result up to that point) and the current value of the array you are processing.

At each step, SCAN evaluates the LAMBDA value, updates the accumulator, and generates a new element in the output matrix with the resulting value. This way, you obtain a sequence of accumulated values ​​or progressive transformations.

A typical example is calculating a cumulative total (running total) over a set of values ​​and, from there, also obtaining the relative cumulative frequency. Imagine you have data in A31:A36 and you want the absolute cumulative total:

=SCAN(0; A31:A36; LAMBDA(accum; param1; accum + param1))

This formula iterates through A31:A36, adding each value to the previous total. The result is an array with the same number of elements as the original range, but each position displays the cumulative total up to that point.

From that cumulative total, it's easy to calculate the cumulative percentage frequency by dividing each cumulative total by the overall total. You could, for example, first define the total using SUM and then apply SCAN again:

=LET(total; SUM(A31:A36); SCAN(0; A31:A36; LAMBDA(accum; param1; (accum + param1)))/total)

In this case, LET assigns to total the sum of the entire range A31:A36. Then SCAN generates the sequence of cumulative values ​​and, by dividing it by total, you obtain the relative cumulative frequency for each step , all in a single matrix formula.

REDUCE function: reduction to a single accumulated value

The REDUCE function also iterates through an array by applying a LAMBDA function to each element, but unlike SCAN, here you are only interested in obtaining the final result of the accumulation process. That is, it performs the same type of iteration as SCAN, but only returns the last value in the accumulator.

Its syntax is:

=REDUCE(; array; LAMBDA(accumulator; value))

Just like in SCAN, the initial_value sets the starting point of the accumulator, the matrix is ​​the range to be processed, and LAMBDA has as parameters the current accumulator and the value being read at that moment.

A very typical use is to calculate a running sum or a cumulative operation where you are only interested in the last result . For example, to sum A1:A6 using REDUCE you could write:

=REDUCE(0; A1:A6; LAMBDA(accum; param1; accum + param1))

Here, REDUCE iterates through A1:A6 and at each step updates the cumulative value by adding the value of the current cell (param1). At the end, it returns a single value: the total sum. It's conceptually similar to using SUM, but with REDUCE you can define any more complex accumulation logic, not just simple sums.

The power of REDUCE lies in its ability to work with the previous result at each step and continue applying operations until the process is complete. This allows you to implement sophisticated custom calculations that were traditionally handled with loops in macros.

Creating custom functions with LAMBDA and the Name Manager

One of LAMBDA's most powerful features is its ability to convert any formula into a user function using Excel's Name Manager. This allows your function to have its own name and be used like any other native function in the program.

The typical workflow is this: first, test the LAMBDA function in a cell , including both the definition and the call with example arguments. Once you verify that it works correctly and returns the expected result, copy the part corresponding to the LAMBDA definition (without the final call) and paste it into the Name Manager.

In the Name Manager, you create a new name (for example, MyVATFunction, MyDiscount, MyWeightedAverage, etc.) and, in the "Refers to" field, you enter:

=LAMBDA(var1; var2; …; varN; calculation)

From that moment on, in any cell of your workbook you can call the function by writing its name as if it were a built-in function , passing the parameter values ​​in the same order in which you defined them.

This has two clear advantages: firstly, it makes your spreadsheets more readable (instead of seeing lengthy formulas, you see a function with a descriptive name); secondly, it centralizes the logic in one place. If you later want to change the calculation, simply modify the definition in the Name Manager, and all formulas that use it will be updated automatically.

Practical aspects and additional considerations

To truly leverage LAMBDA and its associated functions, it's helpful to understand some practical aspects of their behavior and requirements . First, these functions are part of modern Excel features, so you need a version that already includes dynamic arrays and the newer LAMBDA, BYROW, BYCOL, and other functions. These are typically available in the latest editions of Microsoft 365.

Another relevant issue is performance : although LAMBDA and traversal functions are very powerful, if you apply them to enormous ranges with very complex logic, the book may take longer to recalculate. It's advisable to design LAMBDA functions with efficiency in mind, avoiding redundant calculations and leveraging structures like LET to define reusable intermediate values.

It's also crucial to maintain a consistent naming convention for parameters and functions . Using descriptive names helps you understand the logic when you revisit the file months later or when someone else needs to work with your workbooks. A parameter named amount, rate, dataRow, or valuesCol is much clearer than simply x or yoa.

Regarding the #CALC! error , it usually appears when Excel is unable to calculate the array expression or when the LAMBDA function doesn't return a valid result. Always verify that your formula has a well-defined output and, if you're working with array functions, that the dimensions are consistent (for example, that you're not combining incompatible size ranges without proper transformation).

Finally, while LAMBDA eliminates the need for VBA in many cases, it doesn't completely replace it. There are situations where macro automation remains the best option, but for a vast number of custom calculations and data transformations, LAMBDA and its associated functions allow you to keep all your work within Excel's conventional formula environment.

Thanks to these possibilities, those who work daily with spreadsheets now have much more flexible tools to design their own calculations, summarize information by rows or columns, traverse matrices completely or partially, generate detailed totals and create new matrices calculated on the fly, all without leaving the formula language they already know.