Put multiple values in one cell with lists and arrays in Excel

MAKEARRAY is one of the Lambda helpers that I avoided using in the past because it was slow with large arrays. There were always better options for working with 2D arrays without iterating over every element in the array.

Nested array support has breathed new life into MAKEARRAY! One of the big advantages it has over a function like MAP, for example, is the fact MAKEARRAY can iterate across different shape sizes. MAP can accept multiple arrays, but the shapes must be identical.

Here's an example that's been testing very well.

2-Step Convolution process:

• Windowedλ extracts overlapping windows

• Convolveλ then deploys MAP to aggregate the windows

/* [TBD] -------------------------------------------------------------------------
Function:        Windowedλ
Category:        TBD
Author:          Patrick H.
Version:         1.1
Dependencies:    MGridλ

Description:
    Extracts all overlapping windows of size depth × width from a 2D array.
    Each window is returned as a nested array wrapped in a list literal,
    producing an array-of-lists structure.

    Windowedλ uses mesh-grid offsets generated from MGridλ to construct
    window coordinates and MAKEARRAY to assemble the full window fabric.

Parameters:
    array_2D   - 2D array (1D arrays and scalars are rejected).
    depth      - Total rows in each window.
    [width]    - Total columns in each window. Defaults to depth.

Notes:
    • Output is an array of lists, not a spilled 2D grid.
    • Use FLATTEN to spill windows into a table if needed.
    • Rejects 1D arrays and scalars to preserve 2D window semantics.
------------------------------------------------------------------------------*/

Windowedλ =
LAMBDA(
    array_2D,
    depth,
    [width],
LET(
    // Optional width
    width,      IF(ISOMITTED(width), depth, width),

    // Reference shape for mesh grid
    kernel,     EXPAND(0, depth, width, 0),

    // Dimensions
    h,          ROWS(array_2D),
    w,          COLUMNS(array_2D),

    // Halting scenario
    InValidDW?, OR(ISTEXT(depth), ISTEXT(width),
                   depth < 1, width < 1,
                   depth > h, width > w),

    // Trivial case
    OneWindow?, AND(h = depth, w = width),

    IF(InValidDW?, #VALUE!,

       IF(OneWindow?, {array_2D},
LET(
    // Window counts
    k_h,     h - depth + 1,
    k_w,     w - width + 1,

    // Mesh grid offsets
    x,       IF(depth = 1, kernel, MGridλ(kernel) - 1),
    y,       IF(width = 1, kernel, MGridλ(kernel, 1) - 1),

    // Windowing
    windows, MAKEARRAY(k_h, k_w,
                       LAMBDA(i, j,
                              INDEX(array_2D, i + x, j + y))),

    windows
)))));




/* [TBD] -------------------------------------------------------------------------
Function:        Convolveλ
Category:        TBD
Author:          Patrick H.
Version:         2.0
Dependencies:    Windowedλ

Description:
    Performs a sliding‑window convolution over a 2D array using a 2D kernel.
    Each window extracted from the input array has the same dimensions as the
    kernel. A reducer function is applied to each window to produce the output.

    If no reducer function is supplied, Convolveλ defaults to a dot‑product
    reducer: SUM(FLATTEN(v) * kernel).

Parameters:
    array       - 2D array to which the kernel is applied.
    kernel      - 2D array defining the sliding window dimensions.
    [function]  - Optional reducer LAMBDA accepting a single array argument.
                  Defaults to SUM(FLATTEN(v) * kernel).

Notes:
    • Returns "#KERNEL!" if the kernel is larger than the input array.
    • Returns "#FUNCTION!" if the reducer is not a valid LAMBDA.
    • Scalars are treated as 1×1 arrays and mapped through the reducer.
    • Reducer behavior is user‑defined; arrays need not be numeric.
------------------------------------------------------------------------------*/

Convolveλ=
LAMBDA(
    array,
    kernel,
    [function],

LET(
    // Array dimensions
    h,                  ROWS(array),    w,    COLUMNS(array),

    // Kernel dimensions
    kernel_height,      ROWS(kernel),
    kernel_width,       COLUMNS(kernel),

    // Kernel must fit inside array
    InvalidKernel?,     OR(kernel_height > h, kernel_width > w),

    // Scalar handling
    IsScalar?,          AND(h = 1, w = 1),
    IsScalarKernel?,    AND(kernel_height = 1, kernel_width = 1),

    // Default reducer = dot product
    function,           IF(ISOMITTED(function),LAMBDA(v, SUM(FLATTEN(v) * kernel)),function),
    InvalidFunction?,   TYPE(function) <> 128,

    // Scalar or scalar‑kernel case → simple MAP
    StandardMap,        LAMBDA(MAP(array, function)),

// Logic gate
    IF(InvalidKernel?, "#KERNEL!",

        IF(InvalidFunction?,"#FUNCTION!",

            IF((IsScalar?) + (IsScalarKernel?),StandardMap(),

LET(
    windows,            Windowedλ(array,kernel_height,kernel_width),
    convolution,        MAP(windows,function),
    convolution

))))));