Data Transformation FormulaGoogle Sheets V8 Engine Verified

Google Sheets CHOOSEROWS: Slice & Reorder Specific Rows Dynamically

CHOOSEROWS extracts specific rows from an array or range by their index numbers. Supports negative indexing to pull rows from the bottom of a dataset.

Formula Syntax:=CHOOSEROWS(array, row_num1, [row_num2, ...])
πŸ’‘ Best Practice: Always try complex formulas on a copy of your file first before applying them to active production data.

Technical Overview & Engine Mechanics

CHOOSEROWS extracts specific rows from an array based on their integer row index positions. This eliminates complex helper formulas when you need to extract the first row, last row, every second row, or top N records from a sorted dataset. It also supports negative index numbers to count backwards from the bottom of an array.

Formula Comparison: CHOOSEROWS vs. Alternatives

Compared to nested INDEX or FILTER formulas, CHOOSEROWS is concise, intuitive, and designed specifically for dynamic array pipelines. Paired with SORT, it creates instantaneous Top-N and Bottom-N leaderboard tables.

Formula Parameters & Arguments

ArgumentTypeRequiredDescription
arrayRangeRequiredThe source array or table.
row_numIntegerRequiredThe row index to extract (1 = first row, -1 = last row).

Real-World Spreadsheet Examples

Explore verified operational formulas used in enterprise reporting and database workflows:

Example 1: Extract Top 3 Highest Rows from Sorted Table

=CHOOSEROWS(SORT(A2:D100, 4, FALSE), 1, 2, 3)
How it works: Pulls the top 3 highest revenue records.

Example 2: Extract Last 5 Rows from Live Log

=CHOOSEROWS(A2:D, -5, -4, -3, -2, -1)
How it works: Uses negative numbers to retrieve the newest 5 transactions from the bottom.

Example 3: Extract Header and Summary Row Only

=CHOOSEROWS(A1:F50, 1, -1)
How it works: Extracts row 1 (headers) and the final row (totals) into a summary card.

Example 4: Extract Top 5 Performing Sales Records

=CHOOSEROWS(SORT(A2:E500, 5, FALSE), 1, 2, 3, 4, 5)
How it works: Sorts deals descending and pulls rows 1 through 5 directly.

Example 5: Extract the Last Row of a Dynamic Array

=CHOOSEROWS(A2:D100, -1)
How it works: Negative index -1 immediately targets the bottom-most row of the array.

Common Errors & Solutions

❌ #VALUE! Row index out of range
Resolution: Ensure row index numbers do not exceed total row count in array.
❌ #VALUE! Row index out of bounds
Resolution: Verify the requested row index does not exceed the total rows in the source array. Use ROWS(array) to verify size.
❌ Index 0 provided
Resolution: Row indexing in CHOOSEROWS is 1-based (1, 2, 3...) or negative (-1, -2...). 0 is invalid.

Performance Pro Tips

  • Pair with CHOOSECOLS to slice arbitrary subsets of both rows and columns.

Frequently Asked Questions: CHOOSEROWS

How do negative numbers work in CHOOSEROWS?

-1 returns the last row, -2 returns the second to last row, and so on.

Can I extract non-consecutive rows?

Yes. You can extract arbitrary rows like CHOOSEROWS(data, 1, 5, 12, 45).

Can I reverse row order using CHOOSEROWS?

Yes: =LET(d, A2:D10, CHOOSEROWS(d, SEQUENCE(ROWS(d), 1, ROWS(d), -1))).

What is the sister function for columns?

CHOOSECOLS performs the identical operation horizontally on columns.

Is CHOOSEROWS supported in Excel?

Yes, Microsoft Excel 365 supports CHOOSEROWS.

Related Google Sheets Formulas