UNION.RANGES
The UNION.RANGES function returns an array that represents a collection representing the union of the specified collections.
UNION.RANGES(compact-mode, range, range…)
compact-mode: A modal value that determines the order of results and shape of the array returned.
compact left (FALSE or 0): Remove gaps (cells not included in any of the ranges) from left to right, starting with the first row that contains a cell included in any of the ranges. This is the default order.
compact up (TRUE or 1): Remove gaps (cells not included in any of the ranges) from top to bottom, starting with the first column that contains a cell included in any of the ranges.
range: A collection of cells. range is a collection consisting of a single range of cells, which may contain any values.
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 array returned will be the same as the specified collection.
UNION.RANGES attempts to construct a rectangular collection from the input collection by removing gaps between array elements. If the resulting array is a rectangle, this is the array returned. If the resulting array is not a rectangle, rows are moved to the end of the first row one after the other so the array returned consists of a single row representing a list of all the array elements.
If any of the input ranges are invalid, the function returns a reference error.
This function replaces the use of a space as the intersect operator in table-spanning references in some other spreadsheet apps, including Numbers ’08 for Mac and Numbers ’09 for Mac.
Examples |
|---|
Given the following table: |
A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
1 | 7 | 8 | ||||
2 | 19 | 20 | ||||
3 | ||||||
4 | ||||||
5 | 30 | 31 |
In the examples, the value from the cell within the given collection that contained the stated array element is noted in parentheses. =UNION.RANGES(FALSE, A1, B2) returns an array that is 2 rows by 1 column. The values can be extracted from the array using the INDEX function. =INDEX(UNION.RANGES(FALSE, A1, B2), 1, 1, 1) returns 7 (A1), the value in the first row of the array. =INDEX(UNION.RANGES(FALSE, A1, B2), 2, 1, 1) returns 20 (B2), the value in the second row of the array. =UNION.RANGES(TRUE, A1, B2) returns an array that is 1 row by 2 columns. The value in the first column of the array is 7 (A1). The value in the second column of the array is 20 (B2). =VLOOKUP(7, UNION.RANGES(FALSE, A1, B2), 2, 0) returns a range error because, as seen in the first example, the array returned is only one column wide. =VLOOKUP(7, UNION.RANGES(TRUE, A1, B2), 2, 0) returns 20 because, as seen in the second example, the array returned is 1 row by 2 columns. The value in the second column corresponding to the search value of 7 is 20. =UNION.RANGES(FALSE, A1:B1, E5) or =UNION.RANGES(TRUE, A1:B1, E5) returns an array that is 1 row by 3 columns. The values in the one-row array are 7 (A1), 8 (B1) and 30 (E5). =UNION.RANGES(FALSE, A1:B2, D4:F5) returns an array that is 1 row by 10 columns. The values are 7 (A1), 8 (B1), 19 (A2), 20 (B2), 0 (D4), 0 (E4), 0 (F4), 0 (D5), 30 (E5) and 31 (F5). =UNION.RANGES(TRUE, A1:B2, D4:F5) returns an array that is 2 rows by 5 columns. The values in the first row are 7 (A1), 8 (B2), 0 (D4), 0 (E4) and 0 (F4). The values in the second row are 19 (A2), 20 (B2), 0 (D5), 30 (E5) and 31 (F5). |