Exceljet

Quick, clean, and to the point

Excel CHOOSEROWS Function

Excel CHOOSEROWS function
Summary 

The Excel CHOOSEROWS function returns specific rows from an array or range. The rows to return are provided as numbers in separate arguments. Each number corresponds to the numeric index of a row in the given array.

Purpose 
Return specific rows from an array
Return value 
Extracted rows in a single array
Syntax 
=CHOOSEROWS (array, row_num1, [row_num2], ...)
Arguments 
  • 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.
Version 
Usage notes 

The Excel CHOOSEROWS function returns specific rows from an array or range. The rows to return are provided as numbers in separate arguments. Each number corresponds to the numeric index of a row in the source array.

The first argument in the CHOOSEROWS function is array. Array can be a range, or an array from another formula. Additional arguments are in the form row_num1, row_num2, row_num3, etc. Each number represents a specific row to extract from the array, and should be supplied as a whole number.

Basic usage

To get rows 1 and 3 from an array, you can use CHOOSEROWS like this:

=CHOOSEROWS(array,1,3) // rows 1 and 3

To get the same two rows in reverse order:

=CHOOSEROWS(array,3,1) // rows 3 and 1

The result from CHOOSEROWS is always a single array.

With array constants

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, the formula in H3 is:

=CHOOSEROWS(B3:F9,{1,4,7})

With the array constant {1,4,7} given as the second argument, CHOOSEROWS returns rows 1, 4, and 7:

CHOOSEROWS function with array constant

The array constant can be provided in the form {1,2,3} or {1;2;3}.

With arrays

As seen above, you can use an array constant as the second argument in CHOOSEROWS to indicate rows. You can also use an array created with a formula. For example, the formula below uses CHOOSEROWS and the SEQUENCE function to reverse the order of rows in an array:

=CHOOSEROWS(B3:D9,SEQUENCE(ROWS(B3:D9),,ROWS(B3:D9),-1))

When given a 7-row range or array, SEQUENCE returns {7;6;5;4;3;2;1} to CHOOSEROWS, and CHOOSEROWS returns the 7 rows in reverse order:

CHOOSEROWS function to reverse row order

The formula returns all the rows in Array, starting with the last row.

Download 100+ Important Excel Functions

Get over 100 Excel Functions you should know in one handy PDF.