Arrays
Excel supots arrays of values, often conceptualized as ranges. More complex data types (i.e. non-contiguous ranges) sometimes need to be handled.
Contents
Array Functions
Spilling
If an Excel function returns multiple values, it will spill them onto the following rows. This is sometimes ideal, and other times less than ideal.
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.