MAP

What it does

MAP(array1, [array2, …], lambda)

MAP(array1, [array2, …], lambda) applies a calculation to corresponding positions of one or more arrays and returns the resulting array. The lambda goes last and takes one parameter per input array.

=MAP({1;2;3},LAMBDA(x,x*x)) returns a column containing 1, 4 and 9. With two equally shaped arrays, =MAP({1;2},{10;20},LAMBDA(x,y,x+y)) returns 11 and 22. Positions are paired; MAP does not form every possible pair of inputs.

Try it

Square the example column, then change x*x to IF(x>1,x*x,0). Use arrays with the same shape when adapting the two-input example. Mismatched shapes need their own compatibility checks.

Quirks

Microsoft documents #VALUE! when the final argument is not a valid lambda or its parameter count is wrong. A result must fit one output position; a lambda that returns an array introduces a separate nested-array problem rather than automatically expanding the result.

A direct error argument and an error inside an array are observably different. On the recorded Excel build, MAP(NA(),LAMBDA(x,IFERROR(x,3))) returns #N/A before invoking the lambda, while MAP({#N/A},LAMBDA(x,3)) returns 3. The array element can reach the lambda even though the same error supplied directly stops the helper.

The result still needs space in the worksheet. A blocked spill can produce #SPILL!; it does not change the array calculation itself. Try it shows the array without placing it in a workbook.

How to implement

Separate the last argument from the input arrays. Validate the callable and its parameter count without treating it as a displayed error. For a shared input shape, allocate that output shape and visit each coordinate. Read one value from each input at that coordinate, bind the lambda's parameters, evaluate its body and store the scalar result at the same coordinate.

Keep direct argument errors distinct from errors held in an array or referenced cell. Passing an array element through the lambda is necessary for formulas that deliberately replace errors. Likewise, preserve any callable carried inside an array value.

Test mismatched input dimensions, reference inputs, a lambda returning an array, and the order of calls to volatile expressions separately. A general array-broadcast rule is not sufficient evidence for MAP's shape rule. The recorded examples below settle the direct-error distinction; they do not settle every shape case.

Rust code

1 map_fn "MAP" (2, 254) [R "array1", R "lambda_or_array2"] resume map_resume => map_task; |s, a, sv| { run(s, map_task(s, a, sv), sv) }

fn map_task(
    site: &Site<'_>,
    a: &[CalcValue],
    sv: &mut EvalServices<'_>,
) -> Step<Box<dyn KernelTask>> {
    // Fn2 roots031/034: a direct first-source error precedes even a valid LAMBDA.
    // Root035 keeps invalid-LAMBDA validation ahead of a later scalar source error.
    scalar_error(&a[0])?;
    let inputs = a.len() - 1;
    let mut sources = Vec::with_capacity(inputs);
    for index in 0..inputs {
        sources.push(prepared(site, a, index, sv)?);
    }
    let f = lambda(a, inputs, inputs)?;
    // Fn5 helper015: later whole-argument errors are invalid MAP sources.
    // Held errors and errors inside arrays still reach the LAMBDA (016/017).
    for value in &a[1..inputs] {
        if value.callable_value().is_none() && matches!(value.core_ref(), CoreRef::Error(_)) {
            return fail(W::Value);
        }
    }
    let lists: Vec<Vec<CalcValue>> = sources.iter().map(items).collect();
    let count = lists.iter().map(Vec::len).max().unwrap_or(1);
    let shape = sources
        .iter()
        .find_map(|source| source.as_array().map(CalcArray::shape))
        .filter(|shape| shape.rows * shape.cols == count)
        .unwrap_or(ArrayShape {
            rows: 1,
            cols: count.max(1),
        });
    Ok(helper(site, f, HelperInput::Map(lists), shape, count))
}

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

Version differences

Microsoft lists MAP for Microsoft 365 and Excel 2024 on Windows and Mac. The build-specific observations below concern error handling. They do not establish that older or later builds share every helper-function rule.

Alternatives

Use an ordinary array expression for a simple elementwise calculation such as squaring. Use BYROW or BYCOL for whole-row or whole-column calculations. REDUCE carries one accumulator through the array; SCAN also returns the intermediate accumulators. MAKEARRAY generates values from row and column indices.

Named variants

No named variants are recorded yet.