Fix Excel REDUCE and VSTACK #VALUE Errors

A user on a popular Excel community forum recently asked: “I’m trying to use REDUCE to loop through a list of IDs and return a 3-row set of data for each ID using VSTACK. Every time I run it, I just get a #VALUE! error. It works fine if I return a single cell, but as soon as I try to stack arrays, it breaks. Why?”

This is a common “gotcha” with Excel’s dynamic array functions. The problem usually isn’t your logic, but how REDUCE handles its very first step.

The Minimal Working Example

Tested on Microsoft 365 (Desktop and Web) as of late 2024.

In this scenario, we have a list of categories in cells A2:A3, and for each category, we want to generate a “Header” and a “Sub-header” row.

The “Broken” Formula:

=REDUCE("", A2:A3, LAMBDA(acc, val, VSTACK(acc, val & " Header", val & " Sub-header")))

Result: This often returns #VALUE! because the initial value (an empty string "") doesn’t match the structure or “shape” of the arrays being stacked in subsequent steps.

The Fixed Formula:

=DROP(REDUCE("Initial", A2:A3, LAMBDA(acc, val, VSTACK(acc, val & " Header", val & " Sub-header"))), 1)

Why This Works

The REDUCE function is designed to “collapse” an array into a single result. When we use it with VSTACK, we are essentially forcing it to build a new, larger array step-by-step.

1. The Accumulator Issue: In the first iteration, acc is equal to your initial_value (e.g., ""). Excel often struggles to VSTACK a single empty string with a multi-column or multi-row array result from your LAMBDA.
2. The “Dummy” Start: By providing a dummy value (like “Initial” or 0) as the first argument, you give the formula a starting point.
3. The DROP Function: Since your final result now starts with that dummy row, we wrap the entire formula in DROP(..., 1). This removes the first row, leaving you with exactly the data you wanted.

Understanding the Arguments

Argument	Role in VSTACK Pattern	Best Practice
initial_value	The starting “seed” for your table.	Use a dummy string or a header row.
array	The list you are iterating through.	Keep this as a single column for easiest logic.
LAMBDA(acc, val, ...)	The calculation engine.	acc represents the table built so far; val is the current item.
VSTACK(acc, result)	The stacking mechanism.	Ensure the result has the same number of columns as your headers.

Adapting to Your Own Data

If you are pulling data from a table based on a list of IDs, your formula will look like this:

=DROP(
    REDUCE("Header", A2:A10,
        LAMBDA(acc, id,
            VSTACK(acc, FILTER(DataRange, DataRange[ID] = id))
        )
    ),
    1
)

Common Traps to Avoid:

• Inconsistent Column Counts: If your FILTER returns 3 columns for the first ID but 4 columns for the second, VSTACK will return a #VALUE! or #N/A error. Use EXPAND or CHOOSECOLS inside the LAMBDA to normalize the width.
• Empty Results: If one of the IDs in your list returns no data, FILTER will return #CALC!. This will break the entire REDUCE chain. Wrap your filter in IFERROR(FILTER(...), "No Data Found").

Frequently Asked Questions

What if I need to stack horizontally instead of vertically? Simply swap VSTACK for HSTACK and use DROP(..., , 1) (note the extra comma) to remove the first column instead of the first row.

Is there a limit to how much data I can stack this way? Yes. While dynamic arrays are powerful, REDUCE combined with VSTACK can become slow on datasets with tens of thousands of rows because Excel recalculates the entire array structure at every single iteration. If your spreadsheet starts lagging, consider using Power Query to combine your data instead.

Can I use this to filter multiple sheets? Yes, but you cannot easily pass Sheet Names as range references directly into REDUCE. You would typically need a helper range containing the sheet names and use INDIRECT, though this often leads to volatile performance. For multi-sheet stacking, the VSTACK(Sheet1:Sheet3!A1:B10) syntax is usually more efficient if the sheets are structured identically.