Troubleshooting “Nested Array” Errors (Arrays inside Arrays)

Excel triggers a #VALUE! error with the hover message “Nested arrays are not supported” (or a #CALC! error in older dynamic array builds) when a formula attempts to store an array inside an individual cell of another array. Excel’s calculation engine requires all arrays to be flat two-dimensional grids composed strictly of scalar values (numbers, text, booleans, or error codes). It cannot generate or render a “jagged” or multi-dimensional array-of-arrays.

Fast Fix

When using LAMBDA helper functions like BYROW, BYCOL, or MAP, ensure the custom calculation returns a single scalar value per iteration (such as wrapping results in SUM, TEXTJOIN, or INDEX). If combining multiple dynamic outputs, use VSTACK or HSTACK rather than attempting to nest array functions directly.

---

Diagnostic Snapshot

Indicator	Detail
Visible Error	#VALUE! or #CALC!
Hover Prompt	“Nested arrays are not supported”
Typical Context	Advanced LAMBDA formulas (BYROW, MAP, SCAN), or nesting FILTER/UNIQUE inside dynamic functions
Engine Limitation	Excel cell grids cannot contain internal array objects
Primary Fix Strategy	Reduce iterative outputs to scalar values or flatten with array-shaping functions

---

Why the Dynamic Array Engine Rejects Nested Arrays

In programming languages like Python or JavaScript, you can easily create arrays of arrays (e.g., [[1, 2], [3, 4, 5]]).

In Excel, every array must ultimately map directly to physical worksheet rows and columns. A single cell coordinate (such as row 3, column B) can hold only one atomic value. It cannot contain a sub-table or list.

What Excel Supports (Flat 2D Grid):
┌──────┬──────┐
│  10  │  20  │
├──────┼──────┤
│  30  │  40  │
└──────┴──────┘

What Triggers "Nested Arrays Not Supported":
┌───────────────┬──────┐
│ [10, 11, 12]  │  20  │  <── Cell contains an array object!
├───────────────┼──────┤      Engine breaks and displays #VALUE!
│ [30, 31]      │  40  │
└───────────────┴──────┘

When a formula design forces Excel to hold an intermediate array within an array cell, the calculation pipeline halts immediately to prevent memory corruption.

---

Common Triggers and How to Fix Them

1. Returning Arrays Inside BYROW or BYCOL

The most common source of this error occurs with the BYROW function. BYROW loops through a range row by row, expecting the inner LAMBDA to summarize each row into one single output value.

• Incorrect (Returns an Array per Row):=BYROW(A2:C10, LAMBDA(r, FILTER(r, r>0)))Why it fails: If a row has multiple numbers greater than 0, FILTER returns an array of numbers. BYROW cannot place an array inside a single row’s output slot.
• Corrected (Returns a Scalar Summary per Row):=BYROW(A2:C10, LAMBDA(r, SUM(FILTER(r, r>0, 0))))
  Wrapping the result in SUM reduces the filtered values into one number per row, satisfying Excel’s flat structure.
• Alternative (Concatenating Text):
  If you need to preserve all elements returned by the row filter, collapse them into a single string using TEXTJOIN:
  =BYROW(A2:C10, LAMBDA(r, TEXTJOIN(", ", TRUE, FILTER(r, r>0, ""))))

2. Passing Dynamic Arrays into MAP

The MAP function traverses one or more arrays element by element. Like BYROW, the custom LAMBDA applied to each element must return an atomic scalar value.

• Incorrect:=MAP(A2:A10, LAMBDA(cell, UNIQUE(FILTER(B2:B100, C2:C100=cell))))UNIQUE generates an array. If MAP attempts to collect multiple arrays across each evaluated cell, it immediately throws #VALUE!.
• Corrected:
  Extract only a specific element or aggregate the array:
  =MAP(A2:A10, LAMBDA(cell, INDEX(UNIQUE(FILTER(B2:B100, C2:C100=cell)), 1)))

If you are developing complex custom functions that generate unexpected values across custom lambdas, review How to Resolve #VALUE! Errors in New LAMBDA Functions.

3. Combining Dynamic Ranges without VSTACK or HSTACK

Before modern array manipulation functions existed, users attempted to stack ranges using array literals like ={FILTER(A2:A10, B2:B10="East"); FILTER(A2:A10, B2:B10="West")}.

Array literal brackets ({ }) only accept static constants, not dynamic formulas. Attempting to nest dynamic functions inside array brackets generates an unrecognized text error or a nested array fault.

• Correct Modern Solution:
  Use VSTACK (vertical stack) or HSTACK (horizontal stack):
  =VSTACK(FILTER(A2:A10, B2:B10="East"), FILTER(A2:A10, B2:B10="West"))VSTACK handles the dynamic dimensions of both arrays internally and combines them into one flat spilled output.

---

Advanced Technique: Dynamic Appending with REDUCE

If you need to run an iterative operation that builds an expanding array dynamically, the correct design pattern is combining REDUCE with VSTACK.

Suppose you want to loop through a list of customer IDs in F2:F5 and pull all matching transaction records from a master table, stacking the results:

=REDUCE(
    A1:C1,
    F2:F5,
    LAMBDA(accumulator, current_id,
        VSTACK(accumulator, FILTER(A2:C100, D2:D100=current_id, {"No Data","",""}))
    )
)

How this avoids the Nested Array error:

1. REDUCE starts with an initial flat structure (the header row A1:C1).
2. On each iteration, it does not store the new result inside a cell. It flattens the accumulator and the new FILTER output together using VSTACK.
3. The engine never encounters an array inside an array; it simply expands the outer dimensional boundary.

If an array formula creates runaway dimensions that spill over the bottom of the worksheet, refer to Troubleshooting “Spill range is too big” (1M+ row limit).