Numbers UNION.RANGES: Compact Modes, Array Shapes and Gap Examples

• Introduced: The original introduction release and date are not established by the checked references.
• Availability: Currently documented in Apple’s Functions Help 15.4 documentation edition; no final supported-version endpoint is established.
• Scope: Shared iWork reference. Individual historical Mac, iPad, iPhone and iCloud endpoints and imported-workbook parity remain unverified.
• Checked: October 14, 2026, against the official UNION.RANGES reference. Coordinate, array-shape and text models were independently checked; no native Numbers test was performed.

UNION.RANGES(compact-mode, range, range…)

At least one range is needed. Each range is a single collection and may contain any values. Additional ranges are optional. In Apple’s reference:

• FALSE or 0 compacts left, removing gaps horizontally from the first included row. Apple calls this the default order.
• TRUE or 1 compacts up, removing gaps vertically from the first included column.

Every formula here supplies the mode explicitly. Calling an order the default does not by itself establish the syntax or result of omitting the leading argument.

The function attempts to form a rectangle after compaction. If the result is not rectangular, it places the rows one after another into a single row. With one collection, the result is that collection. Invalid input references produce a reference error; no exact native error token is asserted here.

The page connects this function to space-operator handling in table-spanning references in older spreadsheet apps, including Numbers ’08 and ’09 for Mac. That historical comparison does not identify the current name’s first release or declare the function retired.

Create VendorUnion, with sixteen columns A:P and twelve rows, with zero header rows, header columns and footer rows. Enter only these six numbers:

Leave every other cell in A1:F5 genuinely empty. Do not type zeros, empty-string formulas or labels into that source rectangle. The output areas below are outside it.

In G1 enter =INDEX(UNION.RANGES(FALSE,A1,B2),1,1) for 7, and in G2 enter =INDEX(UNION.RANGES(FALSE,A1,B2),2,1) for 20. Apple’s compact-left result is two rows by one column.

In H1 enter =INDEX(UNION.RANGES(TRUE,A1,B2),1,1) for 7, and in I1 enter =INDEX(UNION.RANGES(TRUE,A1,B2),1,2) for 20. The compact-up result is one row by two columns.

INDEX reads an element from an array returned by another function. These scalar formulas show the two shapes without assuming a native spill interface. A downstream operation requesting column 2 cannot use a one-column result merely because it contains two elements.

For Apple’s three-cell example, put 1,2,3 in G3:I3. In G4 enter =INDEX(UNION.RANGES(FALSE,$A$1:$B$1,$E$5),1,G3) and fill through I4 for 7,8,30. In G6 enter =INDEX(UNION.RANGES(TRUE,$A$1:$B$1,$E$5),1,G3) and fill through I6. Both modes produce a one-row, three-column array for that selection.

Enter 1 through 10 in A8:J8. In A9 put:

=INDEX(UNION.RANGES(FALSE,$A$1:$B$2,$D$4:$F$5),1,A8)

Fill through J9. Apple’s compact-left example returns this single row:

7,8,19,20,0,0,0,0,30,31.

Now enter 1 through 5 in L8:P8 and 1,2 in K9:K10. In L9 enter:

=INDEX(UNION.RANGES(TRUE,$A$1:$B$2,$D$4:$F$5),$K9,L$8)

Fill across through P9, then fill L9:P9 down through row 10. Expect:

The four zeros correspond to selected but empty cells D4, E4, F4 and D5 in Apple’s example. They are not the gaps outside all input ranges that compaction removes. This distinction is established for the official fixture; it is not a general claim about formula-empty text or every imported blank.

A12 =SUM(A9:J9) and L12 =SUM(L9:P10) both give 115. Equal totals do not prove equal array shapes or matching element positions.

Create CompleteBlocks, another sixteen-column, twelve-row table with no headers. Enter A1:B2 as 2,4 / 6,8, and D4:F5 as 10,12,14 / 16,18,20. All ten selected cells are explicit numbers.

Recreate the A8:J9 and K8:P10 index and formula areas exactly as above, using local references in this new table. The compact-left row is 2,4,6,8,10,12,14,16,18,20. The compact-up rectangle is 2,4,10,12,14 / 6,8,16,18,20.

A12 =SUM(A9:J9) and L12 =SUM(L9:P10) both give 110. However, B12 =SUM(A9:E9) gives 30 for the first five values of the flattened row, while M12 =SUM(L9:P9) gives 42 for the first row of the compact-up rectangle. C12 =SUM(F9:J9) is 80, and N12 =SUM(L10:P10) is 68.

The group totals differ because they select different original cells. UNION.RANGES is not a join on record IDs, and compaction does not establish that values moved beside each other describe the same record. Carry a deliberate alignment policy into any later calculation.

These examples use nonoverlapping ranges in one table. Duplicate-cell handling for overlapping inputs, ordering across tables, error-valued cells and native array display have not been tested here. Do not infer those behaviors from the word union alone.

INTERSECT.RANGES selects shared locations instead. INDEX reads a chosen element, and SUM checks numeric totals. A total cannot replace inspection of the array dimensions and record mapping.

English formulas here use commas between arguments. Apple’s syntax guidance uses semicolons when the decimal separator is a comma. Keep table references local as instructed, and select cells with the formula editor if imported names or qualifiers differ.