Lambda function in Excel: complete guide and practical examples

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

The function LAMBDA in Excel It has brought about a radical change in the way you work with formulas within Microsoft spreadsheets. Thanks to it, you can create your own custom functions using only Excel's formula language, without touching a single line of VBA or classic programmingIt's as if you had the ability to add new native features to the program, designed specifically for you.

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

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

The function LAMBDA It's a tool that lets you 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 converts any formula into a function which you can reuse as many times as you want. Its great 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 These are the names you give to the variables within the function, while calculation It's the formula that uses those parameters to generate a result. Finally, within the second set of parentheses, the... real values that will take those parameters when executing the function.

If you take advantage of the Name manager To create a permanent Lambda function in Excel, the syntax changes slightly, because you define the function but 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, it's important to follow a series of recommendations so that the functions behave as expected And don't waste time debugging complicated errors. One of the most practical ways to start 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 2It'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 by passing the corresponding argument.

A key recommendation to avoid the mistake #CALC! It's about making sure your LAMBDA always returns a resultThis is achieved by clearly including an expression at the end that produces a single value or an array, depending on what you're looking for. If you see the #CALC! error during testing, check that the formula is actually generating something that Excel can display as a result.

Once you have tested the LAMBDA sensor 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 the entire workbook.

Relationship of LAMBDA with the new matrix functions

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

The general idea is that these functions they traverse data ranges (by rows, by columns, or element by element) and, for each element or group of elements, they execute a LAMBDA function that you define yourself. That is, 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.

The following functions related to LAMBDA stand out in particular: REDUCE, MAP, SCAN, BYCOL, BYROW and MAKEARRAYEach one has 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 function BYROW It 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 is 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 matrix or range that you want to iterate through (for example, B2:D7), and the second is a LAMBDA function that receives 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 of 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 function BYCOL It is designed to traverse a matrix by columns instead of rows. It applies a LAMBDA function to each column of the range and returns a result array where each element corresponds to a column.

Its typical syntax is:

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

In this case, the parameter that LAMBDA receives is the full column of the matrix being processed at each step. Similar to BYROW, the function returns a vector, but now 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:D7You 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, in which each position contains the average of that columnThis way you get all the averages in one fell swoop without having to deal with formulas or worry about relative references.

MAKEARRAY function (MAKEARRAYFILE): create computed arrays

The function MAKEARRAY (in some translations 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 rather build the matrix from scratch.

Its general syntax is:

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

The argument rows indicates how many rows the output matrix will have, columns The number of columns is defined, and the LAMBDA function receives as parameters the row and column indices being calculated in each iteration. 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 matrix of 3 rows by 2 columnswhere each value represents a row and column combination (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&col concatenation).

Another interesting use of MAKEARRAY is convert a vector into a matrix controlling how many elements you take. Suppose you want to form an array with the first 6 values ​​of a vertical range. You could first create an array of positions with FILEMAKEARRAY, 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))

LET is used here for define intermediate names (arrPos, arrPosF), a position array 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 function MAP It is used to iterate through one or more matrices at the same time and return a new matrix in which each output element It is calculated by applying a LAMBDA function to the corresponding input element(s). It is equivalent to a classic "map" 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 become part of the output array, preserving the original values. same dimensions than the original matrix.

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 applied transformation 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 function SCAN It is used to examine a matrix by applying a LAMBDA to each value and generating an output matrix where the values ​​are shown. all intermediate values of the accumulation process. It's very similar to REDUCE, but instead of returning only the final result, it retains every step.

Its general syntax is:

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

The first argument, which is optional, is the initial value The first argument is 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 (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 the calculation of a increasing cumulative (running total) over a set of values ​​and, from there, also obtain the relative cumulative frequency. Imagine you have data in A31:A36 and you want the absolute cumulative frequency:

=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 accumulated values ​​and, by dividing it by the total, you obtain for each step the relative cumulative frequencyall in a single matrix formula.

REDUCE function: reduction to a single accumulated value

The function REDUCE It also traverses an array by applying a LAMBDA 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 traversal as SCAN, but only returns the last value of the accumulator.

Its syntax is:

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

Ig just like in SCAN, the initial_value establishes the starting point of the accumulator, the matrix This 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 latest resultFor example, to add A1:A6 using REDUCE you could write:

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

Here, REDUCE iterates through A1:A6 and updates at each step. now Adding the value of the current cell (param1). When finished, it returns a single value: the total sum. It is conceptually similar to using SUM, but with REDUCE you can define any more complex accumulation logic, not just sums.

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

Creating custom functions with LAMBDA and the Name Manager

One of the most powerful aspects of LAMBDA is the possibility of 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 usually this: first, test LAMBDA in a cellincluding 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, you can call the function in any cell of your workbook by typing 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: on the one hand, You make your pages more legible (Instead of seeing lengthy formulas, you see a function with a descriptive name); on the other hand, you centralize 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 benefit from LAMBDA and its associated functions, it is helpful to keep in mind some practical aspects about their behavior and their requirementsFirst, these functions are part of modern Excel features, so you need a version that already includes dynamic arrays and the new functions LAMBDA, BYROW, BYCOL, etc. These are typically available in the latest editions of Microsoft 365.

Another relevant topic is the performanceAlthough LAMBDA functions 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 is also crucial to have a certain order when... naming parameters and functionsUsing descriptive names helps you understand the logic when you return to the file months later or when someone else has to work with your books. A parameter named amount, rate, dataRow, or valuesCol is much clearer than simply x or yoa.

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

Finally, while Lambda avoids the need for VBA in many cases, it doesn't completely replace it. There are situations where automation using macros remains the best option, but for a huge number of custom calculations and data transformations, LAMBDA and its associated functions They will allow you to keep all the work within the conventional Excel 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 cumulatives and create new matrices calculated on the fly, all without leaving the formula language they already know.