Fix Excel #VALUE Error in Nested LAMBDA with Spilled Ranges

A user on an Excel specialized forum recently asked: “I am building a complex calculation using REDUCE to loop through a list. Inside that REDUCE, I have a MAP function that compares values against a spilled range on another sheet (e.g., ‘Data’!A2#). It works perfectly when the data is on the same sheet, but as soon as I reference the spilled range from another sheet, I get a #VALUE! error. Why does Excel break when the range is external?”

This is a notorious limitation in Excel’s current calculation engine. When you nest “Array Mapping” functions (MAP, SCAN, REDUCE, BYROW, BYCOL), Excel occasionally struggles to resolve the pointer to an external spilled range within the nested context. This is often referred to as the “Nested Array of Arrays” limitation.

The One-Click Quick Fix

The most reliable way to fix this is to capture the external spilled range into a local variable using LET before entering the REDUCE function. This “locks” the data into the local calculation scope, preventing the engine from losing the reference during iteration.

=LET(extData, Sheet2!A2#, REDUCE(0, A2:A10, LAMBDA(acc, val, acc + SUM(MAP(extData, LAMBDA(x, IF(x=val, 1, 0)))))))

Step-by-Step Breakdown

If the quick fix doesn’t solve it, the issue is likely how Excel is attempting to pass the external array into the nested LAMBDA. Follow these steps to restructure your formula for maximum stability.

1. Define the External Range Locally

Excel’s engine sometimes fails to resolve the # (spill operator) across sheet boundaries inside a loop. Use LET to define it at the very start of your formula.

2. Use INDEX Instead of Direct References

If MAP still fails inside REDUCE, stop passing the entire extData array into MAP. Instead, use INDEX to reference the specific row/column you need, or use ROWS() and a sequence to iterate.

3. Formula Components Explained

Tested on: Excel for Microsoft 365 (Version 2402) as of 2024.

Argument	Purpose	Why it fails when nested
`initial_value`	The starting point for `REDUCE`.	Usually safe; rarely causes the #VALUE error.
`array`	The range you are iterating over.	If this is an external `Sheet2!A2#`, it may lose context.
`LAMBDA(acc, val...)`	The logic performed on every step.	Cannot return an array into an array (must return a scalar or handled array).
`MAP(external...)`	The nested loop.	Often fails to “see” external spilled ranges when called inside another LAMBDA.

The Reliable Implementation Template

Use this structure to ensure your formula is robust:

=LET(
    targetRange, Sheet2!A2#,
    iteratorRange, A2:A20,
    REDUCE(0, iteratorRange, LAMBDA(accumulator, current_row,
        LET(
            innerCalc, SUM(MAP(targetRange, LAMBDA(inner_val, IF(inner_val = current_row, 1, 0)))),
            accumulator + innerCalc
        )
    ))
)

Why this works:

Scope: targetRange is defined once. Excel no longer has to jump to “Sheet2” during every single iteration of the REDUCE loop.
Atomic Calculations: By performing the MAP inside a nested LET within the REDUCE, you isolate the calculation, making it easier for the engine to resolve the result before passing it back to the accumulator.

Common Traps & Troubleshooting

The “Array of Arrays” Error: Excel does not currently support an array where each element is itself an array. If your nested MAP returns a range of values (instead of a single SUM or MAX), REDUCE will return #VALUE!. Always ensure the output of your nested LAMBDA is a single value per iteration.
Empty Cells in Spilled Ranges: If Sheet2!A2# contains empty cells that have been included in the spill, MAP might return an error if your logic doesn’t handle null values (e.g., trying to perform math on a blank). Wrap your inner logic in IFERROR(..., 0).
Circular References: Ensure the spilled range you are referencing isn’t dependent on the result of the formula you are currently writing. While obvious, this happens frequently when working with multiple dynamic ranges across sheets.

What if I need to apply this to filtered rows only? You should wrap your iteratorRange in the FILTER function before passing it to REDUCE. For example: REDUCE(0, FILTER(A2:A20, B2:B20="Active"), LAMBDA(...)). This is more efficient than checking the condition inside the LAMBDA.

Can I use BYROW instead of MAP? Yes. In many cases, BYROW(targetRange, LAMBDA(r, ...)) is more performant than MAP when you are dealing with multi-column spilled ranges. The same rules apply: define the range in a LET statement first to avoid the #VALUE error.

Does this work in Excel for the Web? Yes, the LAMBDA and helper functions (MAP, REDUCE) are fully supported in Excel for the Web. In fact, the web version sometimes receives updates to the calculation engine before the desktop version, so if a formula works on the web but not on desktop, ensure your Office installation is up to date.