Fix Excel REDUCE and VSTACK Errors for Dynamic Arrays

A user on the Microsoft Tech Community recently asked: “I am trying to use the REDUCE function to loop through a list of IDs and VSTACK the results of a FILTER for each ID. However, I keep getting a #VALUE! error. I think it’s because the number of rows returned by FILTER changes for each ID. How do I make REDUCE handle an accumulator that changes size?”

This is a common hurdle when moving from basic formulas to advanced Lambda-based arrays. When you use REDUCE with VSTACK or HSTACK, the “initial_value” (the seed) often conflicts with the shape of the data being added.

The Minimal Working Example

The most reliable way to stack arrays of varying sizes within REDUCE is to initialize the accumulator with a dummy value and then “drop” that value in the final result.

The Scenario: You have a list of Categories in A2:A4. You want to look at a data table and stack every row that matches those categories into one continuous list.

The Formula:

=DROP(
    REDUCE("Seed", A2:A4,
        LAMBDA(acc, val,
            VSTACK(acc, FILTER(DataRange[TaskName], DataRange[Category]=val))
        )
    ),
    1
)

Tested on Excel for Microsoft 365 (Desktop and Web) as of May 2024.

Why This Works

The REDUCE function is designed to “shrink” an array into a single result, but in modern Excel, that “single result” can be a massive dynamic array.

1. The Seed Value: We start with a string "Seed". This gives the accumulator (acc) a starting point. Without this, if you try to stack an array onto an empty or null value, Excel often fails to determine the dimensions of the resulting array.
2. The Iteration: For every value in A2:A4, the LAMBDA takes the current accumulator (acc) and vertically stacks the result of a FILTER onto the bottom of it.
3. The Growth: Because VSTACK is flexible, it doesn’t matter if the first FILTER returns 2 rows and the second returns 10; it simply appends them.
4. The DROP Function: Since we started with the word "Seed", our final list will have "Seed" in the very first row. DROP(..., 1) removes that first row, leaving you with only your clean data.

Argument Breakdown

Argument	Purpose	Why it matters here
initial_value	The starting value of the accumulator.	We use “Seed” to ensure VSTACK has an existing array to grab onto.
array	The list to iterate over.	These are the criteria (e.g., IDs or Categories) you are searching for.
lambda	The custom function to run.	This contains the logic (like FILTER or XLOOKUP) that finds your data.
acc	The Accumulator variable.	This represents the “running total” of all stacked rows so far.
val	The Current Value variable.	This represents the specific item from the list currently being processed.

Adapting to Your Data

If you are getting a #CALC! error instead of a #VALUE! error, it usually means your FILTER inside the REDUCE function is returning no results for one of your items. You can wrap your filter in IFERROR or IFNA to prevent the entire stack from breaking:

=DROP(
    REDUCE("Seed", A2:A4,
        LAMBDA(acc, val,
            VSTACK(acc, IFERROR(FILTER(Data!B:B, Data!A:A=val), "No Results"))
        )
    ),
    1
)

Frequently Asked Questions

What if I need to stack columns horizontally instead of rows? You would simply swap VSTACK for HSTACK. However, be careful: HSTACK requires all items in the stack to have the same number of rows, or it will fill the gaps with #N/A. If your column lengths vary, you may need to use the EXPAND function to pad them to a uniform height before stacking.

Is there a limit to how much data REDUCE can handle? Yes. While REDUCE is powerful, Excel’s calculation engine has a limit on the number of elements in a single array (roughly 1 million cells). If you are stacking 100,000 rows with 20 columns, you may notice significant calculation lag or a #VALUE! error if the limit is exceeded. For datasets of that scale, Power Query is the recommended tool.

Can I use this to search across multiple sheets? Yes. You can provide a list of sheet names as the array in REDUCE and use INDIRECT within your FILTER function to pull data from different tabs dynamically. Just ensure that all sheets share the same column structure to avoid alignment issues in your final stack.