Array Manipulation LAMBDA Functions

ArrayManipulation.lambda

/*
Excel Array Manipulation LAMBDA functions
Courtesy of Excel Robot

The Microsoft Excel team recent released 11 new Excel functions for combining, shaping, and resizing arrays.
I had already created similar ones for myself using the amazing LAMBDA function.  Since the new functions
are only available to users running Beta Channel, I've renamed my versions and given them the same function 
names so anyone with LAMBDA and LAMBDA helper functions (like MAKEARRAY), and import these LAMBDAs into your
workbook and have nearly the same functionality.

* Feel free to share this collection, appreciate any shout outs to Excel Robot along the way.

Combining Arrays:
VSTACK - https://support.microsoft.com/en-us/office/vstack-function-a4b86897-be0f-48fc-adca-fcc10d795a9c
HSTACK - https://support.microsoft.com/en-us/office/hstack-function-98c4ab76-10fe-4b4f-8d5f-af1c125fe8c2

Shaping Arrays:
TOROW - https://support.microsoft.com/en-us/office/torow-function-b90d0964-a7d9-44b7-816b-ffa5c2fe2289
TOCOL - https://support.microsoft.com/en-us/office/tocol-function-22839d9b-0b55-4fc1-b4e6-2761f8f122ed
WRAPROWS - https://support.microsoft.com/en-us/office/wraprows-function-796825f3-975a-4cee-9c84-1bbddf60ade0
WRAPCOLS - https://support.microsoft.com/en-us/office/wrapcols-function-d038b05a-57b7-4ee0-be94-ded0792511e2

Resizing Arrays:
TAKE - https://support.microsoft.com/en-us/office/take-function-25382ff1-5da1-4f78-ab43-f33bd2e4e003
DROP - https://support.microsoft.com/en-us/office/drop-function-1cb4e151-9e17-4838-abe5-9ba48d8c6a34
CHOOSEROWS - https://support.microsoft.com/en-us/office/chooserows-function-51ace882-9bab-4a44-9625-7274ef7507a3
CHOOSECOLS - https://support.microsoft.com/en-us/office/choosecols-function-bf117976-2722-4466-9b9a-1c01ed9aebff
EXPAND - https://support.microsoft.com/en-us/office/expand-function-7433fba5-4ad1-41da-a904-d5d95808bc38

*/

/*
Name: Stack Arrays Vertically (VSTACK)
Description: Stacks up to ten arrays vertically.
Contributed By: Excel Robot (@ExcelRobot)
Category: Array
*/
VSTACK = LAMBDA(array1, [array2], [array3], [array4], [array5], [array6], [array7], [array8], [array9], [array10],
    LET(
        stack, LAMBDA(a_1, a_2, MAKEARRAY(
            ROWS(a_1) + ROWS(a_2),
            COLUMNS(a_1),
            LAMBDA(i, j,
                IF(
                    i <= ROWS(a_1),
                    INDEX(a_1, i, j),
                    INDEX(a_2, i - ROWS(a_1), j)
                )
            ))),
        t_2, IF(ISOMITTED(array2), array1, stack(array1, array2)),
        t_3, IF(ISOMITTED(array3), t_2, stack(t_2, array3)),
        t_4, IF(ISOMITTED(array4), t_3, stack(t_3, array4)),
        t_5, IF(ISOMITTED(array5), t_4, stack(t_4, array5)),
        t_6, IF(ISOMITTED(array6), t_5, stack(t_5, array6)),
        t_7, IF(ISOMITTED(array7), t_6, stack(t_6, array7)),
        t_8, IF(ISOMITTED(array8), t_7, stack(t_7, array8)),
        t_9, IF(ISOMITTED(array9), t_8, stack(t_8, array9)),
        t_10, IF(ISOMITTED(array10), t_9, stack(t_9, array10)),
        t_10
    )
);

/*
Name: Stack Arrays Horizontally (HSTACK)
Description: Stacks up to ten arrays horizontally.
Contributed By: Excel Robot (@ExcelRobot)
Category: Array
*/
HSTACK = LAMBDA(array1, [array2], [array3], [array4], [array5], [array6], [array7], [array8], [array9], [array10],
    LET(
        r, ROWS(array1),
        stack, LAMBDA(a_1,a_2, MAKEARRAY(
                r,
                COLUMNS(a_1) + COLUMNS(a_2),
                LAMBDA(i,j,
                    IF(
                        j <= COLUMNS(a_1),
                        INDEX(a_1, i, j),
                        INDEX(a_2, i, j - COLUMNS(a_1))
                    )
                )
            )
        ),
        t_2, IF(ISOMITTED(array2), array1, stack(array1, array2)),
        t_3, IF(ISOMITTED(array3), t_2, stack(t_2, array3)),
        t_4, IF(ISOMITTED(array4), t_3, stack(t_3, array4)),
        t_5, IF(ISOMITTED(array5), t_4, stack(t_4, array5)),
        t_6, IF(ISOMITTED(array6), t_5, stack(t_5, array6)),
        t_7, IF(ISOMITTED(array7), t_6, stack(t_6, array7)),
        t_8, IF(ISOMITTED(array8), t_7, stack(t_7, array8)),
        t_9, IF(ISOMITTED(array9), t_8, stack(t_8, array9)),
        t_10, IF(ISOMITTED(array10), t_9, stack(t_9, array10)),
        t_10
    )
);

/*
Name: Reshape Array To One Row (TOROW)
Description: Returns the array as one row.
Contributed By: Excel Robot (@ExcelRobot)
Category: Array
*/
TOROW = LAMBDA(array, [ignore], [scan_by_col],
    LET(
        base, IF(
            ISOMITTED(scan_by_col),
            array,
            IF(scan_by_col = TRUE, TRANSPOSE(array), array)
        ),
        ColCount, COLUMNS(base),
        Data, MAKEARRAY(
            ROWS(base) * ColCount,
            1,
            LAMBDA(i, j,
                LET(
                    val, INDEX(
                        base,
                        ROUNDUP(i / ColCount, 0),
                        MOD(i - 1, ColCount) + 1
                    ),
                    if(ISOMITTED(ignore),val,IF(val = ignore, "~~IGNORE~~", val))
                )
            )
        ),
        TRANSPOSE(FILTER(Data, Data <> "~~IGNORE~~"))
    )
);

/*
Name: Reshape Array To One Column (TOCOL)
Description: Returns the array as one column.
Contributed By: Excel Robot (@ExcelRobot)
Category: Array
*/
TOCOL = LAMBDA(array, [ignore], [scan_by_col],
    LET(
        base, IF(
            ISOMITTED(scan_by_col),
            array,
            IF(scan_by_col = TRUE, TRANSPOSE(array), array)
        ),
        ColCount, COLUMNS(base),
        Data, MAKEARRAY(
            ROWS(base) * ColCount,
            1,
            LAMBDA(i, j,
                LET(
                    val, INDEX(
                        base,
                        ROUNDUP(i / ColCount, 0),
                        MOD(i - 1, ColCount) + 1
                    ),
                    IF(ISOMITTED(ignore), val, IF(val = ignore, "~~IGNORE~~", val))
                )
            )
        ),
        FILTER(Data, Data <> "~~IGNORE~~")
    )
);

/*
Name: Choose Rows From Array (CHOOSEROWS)
Description: Returns an array made up of the rows of the provided array 
   specified by row numbers.
Contributed By: Excel Robot (@ExcelRobot)
Category: Array
*/
CHOOSEROWS = LAMBDA(array, row_num1, [row_num2], [row_num3], [row_num4], [row_num5], [row_num6], [row_num7], [row_num8], [row_num9], [row_num10],
    LET(
        t_1, tocol(row_num1),
        t_2, IF(ISOMITTED(row_num2), t_1, VSTACK(t_1, tocol(row_num2))),
        t_3, IF(ISOMITTED(row_num3), t_2, VSTACK(t_2, tocol(row_num3))),
        t_4, IF(ISOMITTED(row_num4), t_3, VSTACK(t_3, tocol(row_num4))),
        t_5, IF(ISOMITTED(row_num5), t_4, VSTACK(t_4, tocol(row_num5))),
        t_6, IF(ISOMITTED(row_num6), t_5, VSTACK(t_5, tocol(row_num6))),
        t_7, IF(ISOMITTED(row_num7), t_6, VSTACK(t_6, tocol(row_num7))),
        t_8, IF(ISOMITTED(row_num8), t_7, VSTACK(t_7, tocol(row_num8))),
        t_9, IF(ISOMITTED(row_num9), t_8, VSTACK(t_8, tocol(row_num9))),
        rows_to_select, IF(ISOMITTED(row_num10), t_9, VSTACK(t_9, tocol(row_num10))),
        row_cnt, ROWS(array),
        index_list, MAKEARRAY(
            ROWS(rows_to_select),
            COLUMNS(array),
            LAMBDA(i_pos, j_pos,
                INDEX(rows_to_select, i_pos, 1) + (j_pos - 1) * row_cnt
            )
        ),
        result, WRAPCOLS(
            INDEX(TOCOL(array,,TRUE), TOCOL(index_list,,TRUE)),
            ROWS(rows_to_select)
        ),
        result
    )
);
/*
Name: Choose Columns From Array (CHOOSECOLS)
Description: Returns an array made up of the columns of the provided array 
   specified by column numbers.
Contributed By: Excel Robot (@ExcelRobot)
Category: Array
*/
CHOOSECOLS = LAMBDA(array, col_num1, [col_num2], [col_num3], [col_num4], [col_num5], [col_num6], [col_num7], [col_num8], [col_num9], [col_num10],
    LET(
        t_1, TOCOL(col_num1),
        t_2, IF(ISOMITTED(col_num2), t_1, VSTACK(t_1, TOCOL(col_num2))),
        t_3, IF(ISOMITTED(col_num3), t_2, VSTACK(t_2, TOCOL(col_num3))),
        t_4, IF(ISOMITTED(col_num4), t_3, VSTACK(t_3, TOCOL(col_num4))),
        t_5, IF(ISOMITTED(col_num5), t_4, VSTACK(t_4, TOCOL(col_num5))),
        t_6, IF(ISOMITTED(col_num6), t_5, VSTACK(t_5, TOCOL(col_num6))),
        t_7, IF(ISOMITTED(col_num7), t_6, VSTACK(t_6, TOCOL(col_num7))),
        t_8, IF(ISOMITTED(col_num8), t_7, VSTACK(t_7, TOCOL(col_num8))),
        t_9, IF(ISOMITTED(col_num9), t_8, VSTACK(t_8, TOCOL(col_num9))),
        cols_to_select, IF(
            ISOMITTED(col_num10),
            t_9,
            VSTACK(t_9, TOCOL(col_num10))
        ),
        col_cnt, COLUMNS(array),
        index_list, MAKEARRAY(
            ROWS(array),
            ROWS(cols_to_select),
            LAMBDA(i_pos, j_pos,
                INDEX(cols_to_select, j_pos, 1) + (i_pos - 1) * col_cnt
            )
        ),
        result, WRAPROWS(
            INDEX(TOCOL(array), TOCOL(index_list)),
            ROWS(cols_to_select)
        ),
        result
    )
);

/*
Name: Take Rows Or Columns From Array (TAKE)
Description: Returns rows or columns from the start or end of an array.
   Use negative numbers to return bottom rows or right most columns.
Contributed By: Excel Robot (@ExcelRobot)
Category: Array
*/
TAKE = LAMBDA(array, rows, [columns],
    LET(
        row_count, IF(ISOMITTED(rows), ROWS(array), MIN(ABS(rows), ROWS(array))),
        col_count, IF(
            ISOMITTED(columns),
            COLUMNS(array),
            MIN(ABS(columns), COLUMNS(array))
        ),
        first_row, IF(
            ISOMITTED(rows), 
            1, 
            IF(rows > 0, 1, ROWS(array) + rows + 1)
        ),
        first_col, IF(
            ISOMITTED(columns),
            1,
            IF(columns > 0, 1, COLUMNS(array) + columns + 1)
        ),
        result, MAKEARRAY(
            row_count,
            col_count,
            LAMBDA(i, j, INDEX(array, i + first_row - 1, j + first_col - 1))
        ),
        result
    )
);

/*
Name: Drop Rows Or Columns From Array (DROP)
Description: Returns array with rows or columns dropped from the start or end.
   Use negative numbers to drop bottom rows or right most columns.
Contributed By: Excel Robot (@ExcelRobot)
Category: Array
*/
DROP = LAMBDA(array, rows, [columns],
    LET(
        row_count, ROWS(array) - ABS(rows),
        col_count, IF(
            ISOMITTED(columns),
            COLUMNS(array),
            COLUMNS(array) - ABS(columns)
        ),
        first_row, IF(rows < 0, 1, rows + 1),
        first_col, IF(
            ISOMITTED(columns),
            1,
            IF(columns < 0, 1, columns + 1)
        ),
        result, MAKEARRAY(
            row_count,
            col_count,
            LAMBDA(i, j,
                INDEX(array, i + first_row - 1, j + first_col - 1)
            )
        ),
        result
    )
);

/*
Name: Choose Rows From Array (CHOOSEROWS)
Description: Returns an array made up of the rows of the provided array 
   specified by row numbers.
Contributed By: Excel Robot (@ExcelRobot)
Category: Array
*/
CHOOSEROWS = LAMBDA(array, col_num1, [col_num2], [col_num3], [col_num4], [col_num5], [col_num6], [col_num7], [col_num8], [col_num9], [col_num10],
    LET(
        t_1, TOCOL(col_num1),
        t_2, IF(ISOMITTED(col_num2), t_1, HSTACK(t_1, TOCOL(col_num2))),
        t_3, IF(ISOMITTED(col_num3), t_2, HSTACK(t_2, TOCOL(col_num3))),
        t_4, IF(ISOMITTED(col_num4), t_3, HSTACK(t_3, TOCOL(col_num4))),
        t_5, IF(ISOMITTED(col_num5), t_4, HSTACK(t_4, TOCOL(col_num5))),
        t_6, IF(ISOMITTED(col_num6), t_5, HSTACK(t_5, TOCOL(col_num6))),
        t_7, IF(ISOMITTED(col_num7), t_6, HSTACK(t_6, TOCOL(col_num7))),
        t_8, IF(ISOMITTED(col_num8), t_7, HSTACK(t_7, TOCOL(col_num8))),
        t_9, IF(ISOMITTED(col_num9), t_8, HSTACK(t_8, TOCOL(col_num9))),
        cols_to_select, IF(
            ISOMITTED(col_num10),
            t_9,
            HSTACK(t_9, TOCOL(col_num10))
        ),
        col_cnt, COLUMNS(array),
        index_list, MAKEARRAY(
            ROWS(array),
            ROWS(cols_to_select),
            LAMBDA(i_pos, j_pos,
                INDEX(cols_to_select, j_pos, 1) + (i_pos - 1) * col_cnt
            )
        ),
        result, WRAPCOLS(
            INDEX(TOCOL(array), TOCOL(index_list)),
            ROWS(cols_to_select)
        ),
        result
    )
);

/*
Name: Choose Columns From Array (CHOOSECOLS)
Description: Returns an array made up of the columns of the provided array 
   specified by column numbers.
Contributed By: Excel Robot (@ExcelRobot)
Category: Array
*/
CHOOSECOLS = LAMBDA(array, row_num1, [row_num2], [row_num3], [row_num4], [row_num5], [row_num6], [row_num7], [row_num8], [row_num9], [row_num10],
    LET(
        t_1, tocol(row_num1),
        t_2, IF(ISOMITTED(row_num2), t_1, HSTACK(t_1, tocol(row_num2))),
        t_3, IF(ISOMITTED(row_num3), t_2, HSTACK(t_2, tocol(row_num3))),
        t_4, IF(ISOMITTED(row_num4), t_3, HSTACK(t_3, tocol(row_num4))),
        t_5, IF(ISOMITTED(row_num5), t_4, HSTACK(t_4, tocol(row_num5))),
        t_6, IF(ISOMITTED(row_num6), t_5, HSTACK(t_5, tocol(row_num6))),
        t_7, IF(ISOMITTED(row_num7), t_6, HSTACK(t_6, tocol(row_num7))),
        t_8, IF(ISOMITTED(row_num8), t_7, HSTACK(t_7, tocol(row_num8))),
        t_9, IF(ISOMITTED(row_num9), t_8, HSTACK(t_8, tocol(row_num9))),
        rows_to_select, IF(ISOMITTED(row_num10), t_9, HSTACK(t_9, tocol(row_num10))),
        row_cnt, ROWS(array),
        index_list, MAKEARRAY(
            ROWS(rows_to_select),
            COLUMNS(array),
            LAMBDA(i_pos, j_pos,
                INDEX(rows_to_select, i_pos, 1) + (j_pos - 1) * row_cnt
            )
        ),
        result, WRAPROWS(
            INDEX(TOCOL(array, , TRUE), TOCOL(index_list)),
            ROWS(rows_to_select)
        ),
        result
    )
);

/*
Name: Expand Array (EXPAND)
Description: Expands the dimensions of an array to a specified size padding extra
   space with #N/A! or optional specified value.
Contributed By: Excel Robot (@ExcelRobot)
Category: Array
*/
EXPAND = LAMBDA(array, rows, [columns], [pad_with],
    LET(
        row_count, MAX(rows, ROWS(array)),
        col_count, IF(
            ISOMITTED(columns),
            COLUMNS(array),
            MAX(columns, COLUMNS(array))
        ),
        max_row, ROWS(array),
        max_col, COLUMNS(array),
        missing, IF(ISOMITTED(pad_with), NA(), pad_with),
        MAKEARRAY(
            row_count,
            col_count,
            LAMBDA(i, j, IF(OR(i > max_row, j > max_col), missing, INDEX(array, i, j)))
        )
    )
);

I'm getting a #NAME error with CHOOSEROWS and CHOOSECOLUMNS - I think it's because WRAPROWS and WRAPCOLS aren't defined.

I have a 365 home license - fully up to date - and those two functions are missing.

Microsoft® Excel® for Microsoft 365 MSO (Version 2207 Build 16.0.15427.20182) 64-bit