Quick Navigation
CHOOSEROWS Function
Summary
The CHOOSEROWS function extracts specific rows from an array, returning them in a new dynamic array. This powerful function enables precise row selection by position, supporting both positive and negative indices for flexible data extraction and manipulation.
Syntax
CHOOSEROWS(array, row_num1, [row_num2], …)Parameters
| Parameter | Type | Required | Description |
|---|---|---|---|
| array | Array |
Yes | The source array containing the rows to extract |
| row_num1 | Number |
Yes | First row position to return (positive from top, negative from bottom) |
| row_num2 | Number |
No | Additional row positions (optional, can repeat values) |
Using the CHOOSEROWS Function
CHOOSEROWS revolutionizes array manipulation by allowing direct row selection without complex INDEX formulas or helper columns. Use it to extract headers, specific data rows, create custom summaries, or rearrange data structures dynamically.
Common CHOOSEROWS Examples
Select Multiple Specific Rows
=CHOOSEROWS(A2:B7,1,3,5,1)Returns rows 1, 3, 5, and row 1 again from range A2:B7, maintaining column structure.
Using Array Constants for Row Selection
=CHOOSEROWS(A2:B7,{1,3},5,1)Selects rows 1, 3, 5, and 1 using array constant {1,3} for first two positions.
Select Last Rows with Negative Indices
=CHOOSEROWS(A2:B7,-1,-2)Returns last row (row 6) and second-to-last row (row 5) from A2:B7.
Frequently Asked Questions
Common Errors and Solutions
#VALUE!
Cause: Row number is zero or exceeds array row count (absolute value)
Solution: Verify row_num values are between 1 and array row count, or -1 to -array row count
Notes
- Positive numbers count from row 1 (top) of array
- Negative numbers count from last row (-1 = last row, -2 = second-to-last)
- Row numbers can be repeated to duplicate rows in output
- Works with both range references and dynamic array formulas
- Preserves original column structure and data types
Compatibility
Available in: Excel 365, Excel 2021
Not available in: Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel 2007
Content last reviewed: December 9, 2025
Update frequency: As needed
Excel versions tested: Excel 365, Excel 2021