Forum Discussion
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:
- sliding-window logic
- multi-pass transformations
- tree/graph-like iterative algorithms in Excel
- dynamic partitioning of ranges
- recursive constructions implemented with SCAN/REDUCE
4 Replies
That makes my routine use of thunks look laboured!
= LET( nestedRangesϑ, MAP({1;2;3;4}, LAMBDA(k, THUNK(TAKE(range, k)))), MAP(nestedRangesϑ, SUMϑ) ) where SUMϑ = LAMBDA(ϑ, SUM(ϑ( )));I seem to remember from the past OFFSET creating usable entities that would be errors on the grid. One trick I have used in the past is range intersection which might be used to pick a specific field from a record.
Combining the best of both worlds, we could simplify the beginning to:
nestedRangesϑ, MAP(TAKE(range, {1; 2; 3; 4}), THUNK)
Great stuff! Very interesting... thank you for laying it out in complete detail.
Under 2.2 Deferred Evaluation, you've hinted towards "aggregation functions that re-evaluate their inputs individually". So far, I've found the xxIF(S) family functions can handle an "array of ranges", as well as SUBTOTAL (but not AGGREGATE). Are you aware of any others?
Examples:
// Running count by category = LET( cat, TOROW(UNIQUE(SORT(Category))), VSTACK(cat, COUNTIFS(TAKE(Category, SEQUENCE(ROWS(Category))), cat)) ) // Running total by category = LET( cat, TOROW(UNIQUE(SORT(Category))), VSTACK(cat, SUMIF(TAKE(Category, SEQUENCE(ROWS(Category))), cat, Amount)) ) // Running total by row and column = SUBTOTAL(9, TAKE(range_2d, SEQUENCE(ROWS(range_2d)), SEQUENCE(, COLUMNS(range_2d)))) // Running total by row and column, last to first = SUBTOTAL(9, TAKE(range_2d, SEQUENCE(ROWS(range_2d),, ROWS(range_2d), -1), SEQUENCE(, COLUMNS(range_2d), COLUMNS(range_2d), -1))) // Running total by row --> equivalent to: = SCAN(, BYROW(range_2d, SUM), SUM) = SUBTOTAL(9, TAKE(range_2d, SEQUENCE(ROWS(range_2d)))) // Running total by column --> equivalent to: = SCAN(, BYCOL(range_2d, SUM), SUM) = SUBTOTAL(9, TAKE(range_2d,, SEQUENCE(, COLUMNS(range_2d)))) // Running total by n x n block = SUBTOTAL(9, TAKE(range_2d, {1;2;3;4}, {1;2;3;4})) // vertical output = SUBTOTAL(9, TAKE(range_2d, {1,2,3,4}, {1,2,3,4})) // horizontal output // Running total for a targeted section = SUBTOTAL(9, TAKE(DROP(range_2d, 1, 1), {1;2;3}, {1,2}))I had already tried this with IF functions, but I didn't realize it also worked with SUBTOTAL. That's interesting!
You can also use an array of ranges as XLOOKUP's return array, which allows it to return the entire array rather than just a single value, as in this example:
=XLOOKUP(5, SEQUENCE(10), TAKE(A1:J10, SEQUENCE(10), SEQUENCE(10)))
