Fix Excel LAMBDA Stack Overflow with INDIRECT References

A user in a popular Excel subreddit recently shared a frustrating issue: “I’m trying to create a recursive LAMBDA to consolidate data from 60 different tabs. I’m using INDIRECT to reference sheet names within the loop, but Excel keeps crashing or giving me a #NUM! error due to a stack overflow. My list is only 60 items long—why is Excel hitting a recursion limit so early?”

This is a classic problem. When you combine recursive LAMBDAs (functions that call themselves) with volatile functions like INDIRECT, Excel’s calculation engine struggles to manage the memory stack. Each call to INDIRECT forces a dependency check that, when nested, triggers the stack overflow.

The Solution: Shift from Recursion to REDUCE

Instead of writing a manual recursive function, the most stable way to process a list of sheet references is using the REDUCE function. REDUCE is an iterative “accumulator” that is internally optimized by Excel to avoid the memory overhead of deep recursion.

Tested on: Excel for Microsoft 365 (Version 2402 or later)

1. The Minimal Working Example

If you have a list of sheet names in a range (e.g., A2:A60) and you want to stack the data from cell B2 of every sheet, use this formula:

=REDUCE("Header", A2:A60, LAMBDA(accumulator, sheet_name,
    VSTACK(accumulator, INDIRECT("'" & sheet_name & "'!B2"))
))

2. Why This Works

In a standard recursive LAMBDA, Excel must remember every previous state of the function until the very last sheet is processed. This “stacking” of states is what leads to the overflow.

REDUCE handles this differently:

• Iteration vs. Recursion: It processes one item at a time, updating the accumulator and moving on, which is much lighter on memory.
• Volatile Management: While INDIRECT is still volatile, using it within REDUCE prevents the exponential growth of the dependency tree that occurs in manual recursion.

3. Adapting to Your Data

To make this production-ready, you should account for empty sheets or missing ranges to avoid #REF! errors.

Argument	Purpose	Example
Initial_value	The starting point (usually your headers).	"Sales Data" or A1:C1
Array	The list of values to loop through.	SheetList!A2:A50
Lambda	The logic applied to each item.	LAMBDA(a, v, VSTACK(a, ...))
Accumulator (a)	The running total of stacked data.	a
Value (v)	The current sheet name in the loop.	v

Pro Tip: To handle sheets that might not exist or ranges that vary in size, use LET and IFERROR inside your REDUCE block:

=REDUCE(A1:C1, SheetNames, LAMBDA(acc, name,
    LET(
        currentData, IFERROR(INDIRECT("'" & name & "'!A2:C100"), ""),
        VSTACK(acc, FILTER(currentData, CHOOSECOLS(currentData, 1) <> ""))
    )
))

Common Questions

What if my sheets have different numbers of columns?

VSTACK requires the same number of columns in each array. If your sheets vary, use the EXPAND function inside your LAMBDA to force all arrays to a uniform width before stacking them. For example: VSTACK(acc, EXPAND(currentData, , 5, "")) would force every sheet to have 5 columns.

Is there a limit to how many items REDUCE can handle?

While REDUCE avoids the stack overflow of recursion, it is still subject to Excel’s grid limits (1,048,576 rows). If you are stacking data from 100 sheets and each sheet has 20,000 rows, you will hit a #CALC! error. In those cases, Power Query is the superior tool for consolidation.

Why not just use a 3D reference like SUM(‘Sheet1:Sheet60’!B2)?

3D references are great for simple aggregations (SUM, AVERAGE, COUNTA), but they do not work for stacking arrays or text. If you need to see the actual rows of data rather than just a total, the REDUCE + VSTACK method is the only way to do it dynamically without VBA.

Troubleshooting the #NUM! Error

If you still see a #NUM! error:

1. Check for Circular References: Ensure your REDUCE formula isn’t located on one of the sheets it is trying to pull data from.
2. Shorten Sheet Names: Extremely long sheet names combined with INDIRECT can sometimes hit string character limits in complex calculations.
3. Evaluate Step-by-Step: Use the Formula Auditing > Evaluate Formula tool to see exactly which sheet name is causing the break.