Formualizer Docs

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

ArgumentTypeRequiredDefault
anchorrange (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 as SEQUENCE(1), is stored as a scalar and has no spill range.

Examples

Resolve the spill that starts at A1.

Formula =ANCHORARRAY(A1)

Input cells
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)

Input cells
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)

Input cells
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)

Input cells
E
142

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?.

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.

Implementation

Engine source

On this page