Numbers INTERSECT.RANGES: Find Overlapping Cells and Extract Results

• 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 INTERSECT.RANGES reference. Coordinate, array-shape and text models were independently checked; no native Numbers test was performed.

INTERSECT.RANGES(range, range…)

The first collection is required; additional collections are optional. Each is a single range of cells and may contain any values. With one collection, the returned collection is the same one. The official reference allows different input sizes and returns a scalar or an array according to the intersection.

An invalid input reference or an empty intersection produces a reference error. The page does not specify an exact displayed error token for those rules. Ranges on different tables do not intersect, even when their written addresses and values are identical.

Apple also notes that Numbers ’08 and ’09 for Mac used a space as an intersection operator. This is historical operator syntax, not an established introduction date for the current function name or a guarantee that old imported formulas behave identically.

Create OverlapGrid, with eight columns A:H and ten rows, and set header rows, header columns and footer rows to zero. Enter this numeric grid in A1:D4:

The first range A1:C3 occupies rows 1–3 and columns A–C. The second B2:D4 occupies rows 2–4 and columns B–D. Their common coordinates are B2:C3, a two-row, two-column block.

Use INDEX to read a scalar from the returned array. This avoids requiring a particular spill or array-entry interface:

These arithmetic controls apply to the four explicitly numeric cells. They do not establish how every downstream function treats mixed text, logical or error values returned from another range.

Enter numeric 1,2 in F6:G6 and 1,2 in E7:E8. In F7 enter:

=INDEX(INTERSECT.RANGES($A$1:$C$3,$B$2:$D$4),$E7,F$6)

Fill across to G7 and then fill F7:G7 down through row 8. F7:G8 should contain:

F10 =SUM(F7:G8) independently totals 110. The row and column indices are relative to the returned block, not the original worksheet’s row and column numbers.

In H1 put =INDEX(INTERSECT.RANGES(A1:C3,B2:D4,C1:D4),1,1) for 23. In H2 use the same formula with final indices 2,1 for 33. H3 =SUM(INTERSECT.RANGES(A1:C3,B2:D4,C1:D4)) gives 56.

The third range restricts the common block to C2:C3. It cannot add a cell that was absent from the original intersection. A narrower coordinate selection is a different operation from filtering values greater than a threshold.

Create VendorIntersect, with eight columns and six rows, again with no headers or footer. Put 7 in C3. In G1 enter =INTERSECT.RANGES(B1:C6,C3,B2:C4). The sole shared cell is C3, so the documented result is 7.

In G2 enter =INDEX(INTERSECT.RANGES(INDIRECT("B:C"),C),3,1). Apple’s example intersects columns B:C with column C; INDEX then reads the third row of that one-column result, again 7. The INDIRECT reference explicitly allows a range address supplied as text. Keep G outside the referenced columns.

Back on OverlapGrid, H5 =INDEX(INTERSECT.RANGES(A1:B2),2,1) returns 21, showing the documented one-range case.

Copy OverlapGrid as ChangedOverlap and change only B2 from 22 to 122. F7:G8 becomes 122,23 / 32,33. F1 is now 122, F2/F10 are 210, F3 is 52.5, and F4 remains 4. The third-range results H1:H3 remain 23, 33 and 56 because C2:C3 did not change.

On a separate copy of the original called OutsideChange, change only A1 from 11 to 999. All the two-range intersection results remain unchanged because A1 is outside B2:D4. A value can be present in one input range without belonging to their intersection.

A1:A2 and D3:D4 have no shared locations and meet Apple’s reference-error condition. Identically addressed cells in another table also do not become shared locations. Do not treat this function as a value-matching join.

UNION.RANGES combines collections with explicit shape rules. SUM and AVERAGE aggregate the selected values. Array display, exact native error presentation and imported-workbook behavior remain outside these independently calculated exercises.

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.