CHOOSEROWS stops working in Google Sheets when row numbers are invalid, ranges are incorrect, or formulas reference rows that do not exist.
This usually appears as #VALUE!, missing rows, blank outputs, or formulas returning unexpected data.
What does CHOOSEROWS do in Google Sheets?
CHOOSEROWS extracts selected rows from a range.
Syntax:
=CHOOSEROWS(array, row_num1, row_num2)
Example:
=CHOOSEROWS(A1:C10,1,3,5)
Result:
- Row 1
- Row 3
- Row 5
from the selected range.
Useful for:
- Reports
- Dashboards
- Sampling rows
- Reordering datasets
Why is CHOOSEROWS not working in Google Sheets?
Common causes:
- Invalid row number
- Row number exceeds range size
- Wrong range reference
- Blank source data
- Syntax mistakes
- Confusing sheet row numbers with range row numbers
- Deleted or shifted rows
Why am I getting #VALUE! in CHOOSEROWS?
Most common cause:
- Row number exceeds range
Wrong:
=CHOOSEROWS(A1:C5,10)
Problem:
Range only contains 5 rows
Fix:
=CHOOSEROWS(A1:C5,3)
Always verify selected rows exist inside the range.
Why is CHOOSEROWS returning the wrong rows?
Usually because:
- Row positions are misunderstood
Example:
=CHOOSEROWS(A10:C20,2)
Returns:
Second row inside range
Not:
Spreadsheet row 2
Important:
CHOOSEROWS counts rows relative to the selected range.
Example:
A10:C20
A10 = Row 1
A11 = Row 2
A12 = Row 3
Why is CHOOSEROWS returning blank results?
Possible causes:
- Source rows are blank
- Broken upstream formulas
- Empty source range
Test source first:
=A1:C10
Verify actual row values exist.
Why is CHOOSEROWS failing after copying formula?
Problem:
Range reference shifts.
Wrong:
=CHOOSEROWS(A1:C10,2,4)
Dragged sideways:
- Range changes unexpectedly
Better:
=CHOOSEROWS($A$1:$C$10,2,4)
Use absolute references.
Why is CHOOSEROWS returning errors after deleting rows?
Problem:
Source structure changed.
Example:
Original:
Rows 1–10
Formula:
=CHOOSEROWS(A1:C10,5)
After deleting rows:
- Row positions shift
Result:
- Wrong output or errors
Fix:
- Recheck selected row numbers.
Why does CHOOSEROWS fail inside ARRAYFORMULA?
Problem:
=ARRAYFORMULA(CHOOSEROWS(A:C,1,3))
Usually unnecessary.
Better:
=CHOOSEROWS(A:C,1,3)
CHOOSEROWS() already spills arrays automatically.
Avoid redundant ARRAYFORMULA().
Why is CHOOSEROWS slow on large sheets?
Common cause:
Huge ranges.
Bad:
=CHOOSEROWS(A:Z,1,5,10)
Better:
=CHOOSEROWS(A1:Z5000,1,5,10)
Limit range size for performance.
Can CHOOSEROWS select rows in custom order?
Yes.
Example:
=CHOOSEROWS(A1:C10,5,1,3)
Output order:
Row 5
Row 1
Row 3
Useful for:
- Custom reports
- Dashboard ordering
Can CHOOSEROWS select rows from the bottom?
Yes.
Negative row numbers work.
Example:
=CHOOSEROWS(A1:C10,-1)
Returns:
Last row
Example:
=CHOOSEROWS(A1:C10,-2,-1)
Returns:
Last two rows
When should I use CHOOSEROWS?
Best use cases:
- Extracting specific rows
- Dashboard summaries
- Reordering records
- Sampling datasets
Example:
=CHOOSEROWS(A1:F20,1,10,20)
When should I avoid CHOOSEROWS?
Avoid when:
- You need filtering → use
FILTER() - You need column extraction → use
CHOOSECOLS() - You need conditional logic → use
QUERY()
Use the right function for the problem.
CHOOSEROWS vs alternatives
| Formula | Best Use |
|---|---|
| CHOOSEROWS | Select rows |
| CHOOSECOLS | Select columns |
| FILTER | Conditional extraction |
| QUERY | Structured filtering |
| INDEX | Single-cell retrieval |
Best practices for CHOOSEROWS
- Verify row numbers carefully
- Use absolute references
- Avoid unnecessary
ARRAYFORMULA() - Limit huge ranges
- Recheck formulas after deleting rows
- Test source range first
FAQs
Why is CHOOSEROWS returning #VALUE!?
A selected row number exceeds the source range.
Why is CHOOSEROWS returning wrong rows?
Rows are counted relative to the selected range, not spreadsheet row numbers.
Can CHOOSEROWS select rows from the bottom?
Yes. Use negative numbers like -1 for the last row.
Do I need ARRAYFORMULA() with CHOOSEROWS?
Usually no—CHOOSEROWS() already returns arrays.
Why is CHOOSEROWS slow?
Huge full-sheet ranges increase recalculation time.
What is the fastest CHOOSEROWS fix?
Check row positions first—most issues come from invalid row numbers.
Other Google Sheets Fixes: