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

Interesting observation, but I think there may be a distinction between Excel's internal calculation behavior and what can actually be inferred from the formula results.

I'm not aware of any documented Excel behavior indicating that a #VALUE! error contains a deferred/hidden range reference that can later be materialized by MAP, SUM, SCAN, or REDUCE.

In particular, MAP operates on the values supplied to it. If the input array already contains #VALUE!, the error is generally an error value rather than a range reference waiting to be evaluated.

For example, I would be cautious about interpreting:

=MAP(TAKE(A1:A10,{1;2;3;4}),SUM)

as:

#VALUE! → hidden reference → SUM resolves the reference

There are other Excel behaviors that can make this look like reference retention, especially the distinction between references and arrays/values and the fact that some functions preserve reference semantics while others perform value coercion. But that doesn't necessarily mean the error itself contains a deferred range object.

A useful way to investigate this would be to compare the behavior with functions that explicitly require references versus functions that only accept values, and test whether the alleged reference can be independently observed or manipulated.

If this is based on reverse-engineering Excel's calculation engine, it would be interesting to see a minimal reproducible workbook demonstrating that the #VALUE! produced by TAKE/DROP can actually be consumed as a range reference by a downstream function. Without that, I'd describe the internal-reference/thunk explanation as a hypothesis rather than established Excel behavior.