Return specific rows from an array
=CHOOSEROWS(array, row_num1, [row_num2], ...)| Parameter | Description |
|---|---|
array |
The array to extract rows from. |
row_num1 |
The numeric index of the first row to return. |
row_num2 |
[optional] The numeric index of the second row to return. |
To get rows 1 and 3 from an array, you can use CHOOSEROWS like this:
=CHOOSEROWS(A1:A5,1,3) // rows 1 and 3To get the same two rows in reverse order:
=CHOOSEROWS(A1:A5,3,1) // rows 3 and 1CHOOSEROWS will return a #VALUE! error if a requested row number is out of range:
=CHOOSEROWS(A1:A5,6) // returns #VALUE!Another option for specifying which rows to return is to use an array constant like {1,4,7} as the second argument (row_num1). In the example below, t
=CHOOSEROWS(B3:F9,{1,4,7})A nice feature of CHOOSEROWS is that you can use negative row numbers to extract rows from the end of a range. For example, to get the last row of a r
=CHOOSEROWS(range,-1)To get the second to last row, you can use:
=CHOOSEROWS(range,-2)