INTERSECT.RANGES

The INTERSECT.RANGES function returns a single value or an array of values contained at the intersection of the specified collections.

INTERSECT.RANGES(range, range…)

range: A collection of cells. range is a collection consisting of a single range of cells, which may contain any value.

range…: Optionally include one or more additional collections.

Notes

The collections can be of any size and only one collection is required. If only one collection is specified, the collection returned will be the same as the specified collection.
Collections on different tables do not intersect, even if the range of cells on each table is the same. For example:
=INTERSECT.RANGES(Table 1::B, Table 2::B) returns an error.
If any of the input ranges are invalid, the function returns a reference error.
If the intersection is empty, the function returns a reference error. For example:
=INTERSECT.RANGES(B,C), returns an error because B and C do not intersect.
This function is provided primarily for compatibility with other spreadsheet apps. It is also useful when working with referenced ranges where the exact location of individual cells may not be known.
In other spreadsheet apps, including Numbers ’08 for Mac and Numbers ’09 for Mac, a space is used as the intersect operator.

Examples
=INTERSECT.RANGES(INDIRECT("B:C"),C) returns an array containing the values in column C because column C is the intersection or overlapping area, of the first argument, the range returned by the INDIRECT function (columns B and C) and the second argument, column C. These values can be read using the INDEX function. =INTERSECT.RANGES(B1:C6, C3, B2:C4) returns 7, if cell C3 contains 7, as C3 is the only cell referenced by all three arguments specified.

Examples

=INTERSECT.RANGES(INDIRECT("B:C"),C) returns an array containing the values in column C because column C is the intersection or overlapping area, of the first argument, the range returned by the INDIRECT function (columns B and C) and the second argument, column C. These values can be read using the INDEX function.

=INTERSECT.RANGES(B1:C6, C3, B2:C4) returns 7, if cell C3 contains 7, as C3 is the only cell referenced by all three arguments specified.

See alsoINDEX INDIRECT

Helpful?

Formulas and Functions Help

INTERSECT.RANGES

Notes