Array Functions … no documentation, no search, and no clear explanation

Dynamic array "spill" functions have lots of documentation. It's just that the documentation is not segregated from the documentation for the other functions.

Try going here and searching for "array."

Formulas and Functions Help - Apple Support

Here's a list I've compiled of the new functions and operators:

Numbers Dynamic Array functions

Added in Numbers v. 14.4, April 2025

Logical & Info:

BYROW()

BYCOL()

ISOMITTED()

LAMBDA()

LAMBDA.APPLY()

LET()

MAKEARRAY()

MAP()

REDUCE()

SCAN()

Numeric:

RANDARRAY()

MDETERM()

MINVERSE()

MMULT()

MUNIT()

SEQUENCE()

Reference:

AREAS()

CHOOSECOLS()

CHOOSEROWS()

DROP()

EXPAND()

FILTER()

HSTACK()

SORT()

SORTBY()

TAKE()

TOCOL()

TOROW()

UNIQUE()

VSTACK()

WRAPCOLS()

WRAPROWS()

Statistical:

EXPONDIST

Text:

ARRAYTOTEXT()

TEXTSPLIT()

DATE and Time

ISOWEEKNUM()

Changed behavior of “old” functions

OFFSET() can now spill

XLOOKUP() can now spill ?

VLOOKUP() multiple lookup values?

XMATCH()

SUMIFS() will spill

New Operators

# spilled range operator

Used to reference the entire spilled range of a formula by placing # after a reference to a spilling origin cell.

See entry for REDUCE()

@ implicit intersection operator

Adds compatibility with Excel’s dynamic array formula system.

Dynamic array functions were first adopted in Excel, as an improvement to Excel's old "array functions". Thus you can also find lots of Excel examples that can be adapted to Numbers, e.g. this.

SG

Forgot to add.

These can seem overwhelming. But most of us don't need them all. So far I've found FILTER, LET, SORTBY, and UNIQUE to be immediately useful, without too much of a learning curve.

SG

It is true that there is not much documentation.

If you have a backup of your HD you might have an older version of Numbers backed up that you can copy/paste back to your HD. I have an older verion and the latest. I appended the version number to the older one's name so it shows up in "open with" as Numbers 13.1.

It is true that some formulas didn't translate/import/update properly to the new version. I find that unacceptable, given that it was entirely Apple-to-Apple. They should have been able to update everything correctly.

SUMIF(A,B,TRUE) does not sum A if the contents of B are TRUE. The correct formula is =SUMIF(B,TRUE,A). That might be why you are having a problem with it. You may be thinking of SUMIFS which would be =SUMIFS(A,B,TRUE)

@A means the value in column A in the same row as the formula. Without the @ it means the entire column A, other than headers and footers. In the past the letter by itself could mean either thing, depending on context. It makes a difference now because of the array formulas.

I have some issues about array functions sometimes including values in the headers and footers when spilling when I think they shouldn't but I'm new to these functions so I might be misunderstanding something.

It is my wish that Apple would fix the "broken" things before they introduce more new features. There are some bugs that have been in Numbers for years, some of which I consider to be critical calculation errors, others are just annoying bugs. They are still unfixed.