Mastering Dynamic Arrays #2: Selecting Array Rows/Columns

This is the second of what I hope will be a fairly frequent series on mastering the magic of Dynamic Arrays (DAs). The previous post introduced SEQUENCE() which generates 1 and 2 dimensional arrays. This post is about ways to select specific rows or columns from arrays.

NOTE: If this is not new to you, please post for the readers how you do this in the comments. Thanks.

To start, we will create a 2 dimensional array by entering this formula in cell A1.

=SEQUENCE( 5, 7)

A1 is the upper left corner of this array and the only cell with a formula. A1# (note the hashtag, aka pound sign) is the address of the entire array.

Get one row or column using INDEX( array, row number, column number)

To get the array's second row we can use INDEX( ) like so:

=INDEX( A1#, 2, )

A1# is the entire array. 2 is the row we want. By leaving the column argument empty, index retrieves all columns. NOTE! We must include the comma after the row number.

We can retrieve the third column similarly:

=INDEX( A1#, , 3)

Get multiple rows of columns using CHOOSECOLS( array, col_num1, [col_num2], …) and CHOOSEROWS()

INDEX() works well for one row or one column. If we want multiple rows or columns we should use CHOOSEROWS() and CHOOSECOLS(). To get the second and fourth rows we can use this formula:

=CHOOSEROWS( A1#, 2, 4)

To get the second and fourth columns we can use this formula:

=CHOOSECOLS( A1#, 2, 4)

Of course, CHOOSEROWS() and CHOOSECOLS() can retrieve just one row or column so the INDEX() option is a bit redundant so I will be standardizing on CHOOSEROWS() and CHOOSECOLS() in my work.

In the next post we will discuss two other functions for retrieving array sections: TAKE() and DROP().