Excel Arrays
Excel arrays are a data type representing multiple cells worth of data.
Contents
Description
The Excel data model is oriented around cells. However, there is an advanced data type in Excel arrays.
Most commonly, arrays are instantiated by referencing a range of cells, e.g. A1:A10.
An array of literals can be instantiated like:
={1,2,3,4,5}Some Excel formulas also return an array, e.g. FILTER.
Spilling
If a cell's value is set to an array, especially when using Excel functions, it will spill across rows into multiple cells. This is sometimes intended, and sometimes not.
Preventing Spills
To prevent a spill, use the INDEX function to select the number of values desired. The row_num and column_num arguments select the array members to return.
=INDEX($A$1:$Z$100, {1;2;3}, {1,4}){1;2;3} is a vertical array literal, communicating that the first three rows should be selected. Likewise, {1,4} is a horizontal array literal, communicating that the first and fourth columns should be selected. As demonstrated, the selections do not need to be contiguous.
To select the first value in an array, just use {1} as the vertical array literal.