ANCHORARRAY
Returns the current spill range of a dynamic-array anchor cell.
ANCHORARRAY(A1) is the stored form of the spill-range operator A1#
(files written by Excel store _xlfn.ANCHORARRAY(A1)). Both evaluate to
the rectangle that the formula in the anchor cell currently spills into,
as a reference, so range consumers such as SUM, ROWS and INDEX read
it like any other range.
The Formualizer engine in this repository at 0.14.0 plus unreleased changes computes the example results. Known differences from Excel are listed on this page.
Syntax
=ANCHORARRAY(anchor)
Arguments
| Argument | Type | Required | Default |
|---|---|---|---|
anchor | range (range) | Required | — |
Return type
Can return a reference when evaluated in a reference context.
Remarks
- The argument must be written as a single-cell reference (optionally on another sheet) or a defined name that refers to one cell.
- Formualizer policy:
#REF!when the argument is anything else, and when the anchor has no current spill (a value, an empty cell, a scalar result, or a blocked or oversized spill). A formula whose result is a single value, including a 1x1 array such asSEQUENCE(1), is stored as a scalar and has no spill range.
Examples
Resolve the spill that starts at A1.
Formula =ANCHORARRAY(A1)
| A | |
|---|---|
| 1 | =SEQUENCE(3) |
Expected result {1; 2; 3}
Try this formula
This editor runs the published Formualizer 0.14.0 package, so the last digits can differ and functions newer than that release return #NAME?.
Resolve a spill with more than one column.
Formula =ANCHORARRAY(B1)
| B | |
|---|---|
| 1 | =SEQUENCE(2,2) |
Expected result {1, 2; 3, 4}
Try this formula
This editor runs the published Formualizer 0.14.0 package, so the last digits can differ and functions newer than that release return #NAME?.
Resolve the spill of a one-row sequence.
Formula =ANCHORARRAY(F1)
| F | |
|---|---|
| 1 | =SEQUENCE(1,2) |
Expected result {1, 2}
Try this formula
This editor runs the published Formualizer 0.14.0 package, so the last digits can differ and functions newer than that release return #NAME?.
A numeric input cell has no spill range to resolve.
Formula =ANCHORARRAY(E1)
| E | |
|---|---|
| 1 | 42 |
Expected result #REF!
Try this formula
This editor runs the published Formualizer 0.14.0 package, so the last digits can differ and functions newer than that release return #NAME?.
Related functions
FAQ
How does ANCHORARRAY relate to the # operator?
They are the same reference: A1# is written as _xlfn.ANCHORARRAY(A1) in stored files, and both resolve to the anchor's current spill range.
When does ANCHORARRAY return #REF!?
When its argument is not a single-cell reference or single-cell name, or when the anchor cell has no current spill.