When #VALUE! isn’t an error: The hidden reference behavior of Excel’s dynamic arrays

1. Mechanism Overview

Functions such as TAKE, DROP, OFFSET, and INDIRECT normally produce a range when supplied with scalar parameters. When instead a 1-D or 2-D numeric array is passed to one of these parameters, Excel attempts to generate multiple range outputs (one per array element). Since Excel’s grid architecture does not allow a single function call to return multiple ranges in parallel, the function fails. The visible result is an array filled with #VALUE!.

Example patterns:

=TAKE(range, {1;2;3;4}) =DROP(range, {1;2;3;4}) =OFFSET(range, {1;2;3;4}, {1,2,3}) =INDIRECT("A1:B"&{1;2;3;4})

Excel returns:

{ #VALUE!; #VALUE!; #VALUE!; #VALUE! }

Although the values appear identical, each #VALUE! is associated with a distinct internal range reference corresponding to the attempted slice, offset or address.

This is a side effect of Excel’s reference-binding layer, which attaches metadata to #VALUE! propagation structures when a reference-producing function fails after partially resolving its operands.

2. Behavior Characterization

2.1 Reference Retention

Each #VALUE! error produced by the above functions contains a deferred reference representing the range the function attempted to construct.

The reference is not materialized because:

a multi-range return is not allowed, and
the evaluation is aborted at the final conversion stage.

However, the binding phase (operand resolution) has already created a reference object. This object is preserved internally and passed forward along the calculation chain.

2.2 Deferred Evaluation

Functions that inspect operand values (e.g., SUM, MIN, COUNT) cannot extract data from these encapsulated #VALUE! tokens and will propagate the error.

Functions that inspect operand references — such as MAP, SCAN, REDUCE, MAKEARRAY, and aggregation functions that re-evaluate their inputs individually — trigger evaluation at the point of consumption.

Example:

=MAP( TAKE(range, {1;2;3;4}), SUM )

In each MAP iteration:

ref is a #VALUE! carrying an unresolved range reference.
SUM(ref) requests value-level evaluation of that range.
The reference is resolved at that moment only.
The resulting scalar is passed downstream.

Thus, the #VALUE! error acts as a strict thunk.

3. Functional Implications for Formula Construction

3.1 Range-Object Semantics

The technique enables modeling of ranges as first-class reference structures, not arrays of extracted values. This allows:

passing range slices through pipelines
keeping intermediate objects lightweight
avoiding large intermediate spillage
reducing recalculation costs for multi-slice operations

3.2 Lazy Slice Evaluation

Using {n1; n2; …} arrays in index parameters creates a vector of deferred slices. Functions downstream resolve these slices on demand and only for the specific iteration that needs them.

This is especially useful for: