MAKEARRAY

What it does

MAKEARRAY(rows, cols, lambda)

MAKEARRAY(rows, cols, LAMBDA(row,col,calculation)) builds an array by calling a formula once for each coordinate. The lambda receives one-based row and column indices.

Try it

=MAKEARRAY(2,3,LAMBDA(r,c,10*r+c)) spills 11,12,13 above 21,22,23. It is a coordinate-based construction rather than a traversal of an existing array.

Quirks

The lambda takes exactly two parameters. Microsoft documents #VALUE! for an invalid callable, incorrect parameter count, dimensions below one or nonnumeric dimensions. The current code additionally rejects fractional dimensions rather than truncating them.

Each call must supply one cell value. The current helper returns #VALUE! for an array-valued lambda result, even a one-cell array; that differs from SCAN’s singleton-array rule. For example, MAKEARRAY(1,1,LAMBDA(r,c,{1})) takes this error route. A scalar value of one is a different result kind from the array {1}.

Dimensions are validated before the callable. With valid dimensions, a noncallable final argument is #VALUE! even if it is itself an error value. Spill obstructions and allocation limits belong to the workbook or evaluation host.

How to implement

1. Read and coerce the two dimensions. Require finite positive integers; check rows*cols before allocating output storage.
2. Validate that the final argument is callable with two parameters. Keep its captured environment so names inside the lambda resolve as they did where it was created.
3. For each zero-based coordinate (r,c), invoke the lambda with the numbers (r+1,c+1). These are array coordinates, not worksheet row and column addresses.
4. Accept a scalar result at that coordinate. The current helper rejects an array or reference result with #VALUE!; it preserves ordinary worksheet error values as cell results.
5. Return the requested rectangular array. If evaluation can yield, retain the completed cells and next coordinate so resuming does not invoke already completed cells again.

For 10*r+c, the first row uses (1,1), (1,2), (1,3) to produce 11,12,13. The next row begins at (2,1), producing 21. No source array is traversed: the dimensions define the coordinates.

Rust code

6 makearray_fn "MAKEARRAY" (3, 3) [V "rows", V "cols", R "lambda"] resume makearray_resume => makearray_task; |s, a, sv| { run(s, makearray_task(s, a, sv), sv) }

/// A MAKEARRAY dimension: a positive integer, anything else #VALUE!.
fn dimension(site: &Site<'_>, value: &CalcValue) -> Step<usize> {
    use crate::ported::dynamic_array_reshape_family::CoercionError;
    let raw = match coerce_prepared_to_number(value) {
        Ok(raw) => raw,
        Err(CoercionError::WorksheetError(code)) => return fail(code),
        Err(CoercionError::NotModelled) => return Err(site.not_modelled(None)),
        Err(CoercionError::UnsupportedValueKind(_)) => return fail(W::Value),
    };
    if !raw.is_finite() || raw < 1.0 || raw.trunc() != raw {
        return fail(W::Value);
    }
    Ok(raw as usize)
}

fn makearray_task(
    site: &Site<'_>,
    a: &[CalcValue],
    _: &mut EvalServices<'_>,
) -> Step<Box<dyn KernelTask>> {
    let rows = dimension(site, &a[0])?;
    let cols = dimension(site, &a[1])?;
    let f = lambda(a, 2, 2)?;
    let count = rows
        .checked_mul(cols)
        .ok_or_else(|| site.not_modelled(None))?;
    Ok(helper(
        site,
        f,
        HelperInput::MakeArray,
        ArrayShape { rows, cols },
        count,
    ))
}

No implementation has been published with a Handbook test suite for this function.

Version differences

A retained source comment reports =MAKEARRAY(1,1,#REF!) returning #VALUE! in Excel 16.0 build 20430 on 25 October 2026. The earlier the earlier implementation implementation propagated #REF!; the current helper validates the final argument as a callable instead. This is an implementation correction against a recorded Excel answer, not a demonstrated change between Excel builds. That comment does not specify CPU or platform.

Recorded-control source. No same-input comparison across named Excel builds is recorded here.

Alternatives

SEQUENCE is simpler for arithmetic progressions; MAP transforms existing entries; BYROW reduces each row.

Named variants

No named variants are recorded yet.