CHOOSEROWS function
Quick answer
CHOOSEROWS returns the rows you name from a range or array: 1 is the first row and -1 the last, so CHOOSEROWS(range,-1) always gives the latest entry.
- Syntax
=CHOOSEROWS(array, row_num1, [row_num2], ...)- Most common use
=CHOOSEROWS(A2:B5,-1)returns Apr (The latest month)
Use it to pull the top and bottom rows of a sorted list, or specific positions such as every quarter-end month.
- Available in
- Excel 2024 and Microsoft 365
- Category
- Dynamic array functions
- Returns
- An array with the chosen rows.
- Google Sheets
- Available in Google Sheets.
Syntax
=CHOOSEROWS(array, row_num1, [row_num2], ...)
| Argument | Required? | What it is |
|---|---|---|
array | Required | The range or array to take rows from. |
row_num1 | Required | The first row to return; negative counts from the bottom. |
row_num2 | Optional | Optional: more rows. |
Examples
Each result below is calculated from the sample sheet by our formula engine, the same one that grades the practice.
| Cell | Formula | Result | What it shows |
|---|---|---|---|
| D2 | =CHOOSEROWS(A2:B5,-1) | Aprspills 1 × 2 | The latest month |
| D4 | =CHOOSEROWS(A2:B5,1,3) | Janspills 2 × 2 | The first and third rows |
| G2 | =CHOOSEROWS(SORT(A2:B5,2,-1),1) | Aprspills 1 × 2 | The best month: sort by sales, keep row 1 |
Try it live
This is the sample sheet. Change any number or text, or edit a formula, and the results update.
Show the sample data as a table
| Month | Sales | ||||||
| Jan | 1200 | Apr | 1500 | Apr | 1500 | ||
| Feb | 1350 | ||||||
| Mar | 990 | Jan | 1200 | ||||
| Apr | 1500 | Mar | 990 |
Practice CHOOSEROWS
Graded instantly, then re-checked on changed data.
First and last month
A2:B13 is in month order. In D2, return the full rows for the first month and the last month, in that order.Common mistakes
| What you see | Why, and how to fix it |
|---|---|
| #VALUE! | A row number is 0, or beyond the number of rows. |
| The "latest" row isn't the latest | CHOOSEROWS counts positions, not dates. Sort first if position should mean something. |
Tips
- =CHOOSEROWS(A2:C100,1,-1) returns the first and last rows together.
Questions
How do I get the last row of a list in Excel?
=CHOOSEROWS(A2:C20,-1). Negative numbers count from the bottom.
What is the difference between CHOOSEROWS and TAKE?
TAKE keeps a block from the start or end. CHOOSEROWS picks any rows, in any order.
Answers that use CHOOSEROWS
Updated Checked by running: every formula on this page was run by our formula engine on .