BYROW2D and BYCOL2D

=L_BYROW2D(array, LAMBDA(row, expression) )
=L_BYCOL2D(array, LAMBDA(col, expression) )

Download the Template

In the template file, navigate to the General worksheet to see the L_BYROW2D function in action.

Description

L_BYROW2D and L_BYCOL2D are able to return two-dimensional arrays (multiple columns and rows) rather than only one-dimensional arrays (a single column or single row). The idea for these functions came from the post on answers.microsoft.com by Tobias Brandt.

The built-in BYROW and BYCOL functions in Excel are usually simpler to use than the REDUCE function when you want to perform some function on each individual row or column of an array. However, one of the main limitations of these functions is that the expression can only return a single value, resulting in the output of BYROW being a single column or the output of BYCOL being a single row.

L_BYROW2D and L_BYCOL2D are designed to use the same syntax as BYROW and BYCOL, but in the case of BYROW2D, the expression can return a row vector instead of a single value. Likewise for BYCOL2D, the expression can return a column vector instead of a single value.

The following example is similar to L_ROWSUM and L_COLSUM except that both a SUM and AVERAGE are returned.

Lambda Formula

This code for using L_BYROW2D in Excel is provided under the License as part of the LAMBDA Library, but to use just this function, you may copy the following code directly into your spreadsheet.

/**
* Like BYROW, but can return a multi-column array instead of just a single column
*/
L_BYROW2D = LAMBDA(array, function,
LET(doc,"https://www.vertex42.com/lambda/byrow2d.html",
    REDUCE("",
        SEQUENCE(ROWS(array)),
        LAMBDA(acc,i,IF(i=1,
            function(CHOOSEROWS(array,i)),
            VSTACK(acc,function(CHOOSEROWS(array,i)))
        ))
    )
));
/**
* Like BYCOL, but can return a multi-row array instead of just a single row
*/
L_BYCOL2D = LAMBDA(array, function,
LET(doc,"https://www.vertex42.com/lambda/byrow2d.html",
    REDUCE("",
        SEQUENCE(COLUMNS(array)),
        LAMBDA(acc,i,IF(i=1,
            function(CHOOSECOLS(array,i)),
            HSTACK(acc,function(CHOOSECOLS(array,i)))
        ))
    )
));

Name: L_BYROW2D
Description: Like BYROW, but can return a row vector instead of just a single value
Arguments: array, function
Function:
[in the works]

L_BYROW2D Examples