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: To get the same two rows in reverse order: CHOOSEROWS will return a #VALUE! error if a requested row number is out of range:
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: With the array constant {1,4,7} given as the second argument, CHOOSEROWS returns rows 1, 4, and 7:
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: 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:
The formula returns all the rows in Array, starting with the last row.
Notes
CHOOSEROWS will return a #VALUE error if a row number is out of range.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
