Excel functions / Dynamic arrays
CHOOSEROWS function
Returns the rows you name from an array, in the order you name them.
Syntax
=CHOOSEROWS(array, row_num1, [row_num2], …)
Arguments
array: The array to pick rows from.row_num: A row number: 1 is the first, -1 the last.
When to use it
CHOOSEROWS returns the rows you list, by number, in the order you list them. A negative number counts from the end, so -1 is the last row.
Examples
The first and last items
A B C 1 Item Pen 2 Pen Ruler 3 Stapler 4 Folder 5 Tape 6 Glue 7 Ruler In C1:
=CHOOSEROWS(A2:A7, 1, -1)→ Pen; RulerThe top three sales, largest first
A B C D E F G 1 Region Rep Sales North Ana 1200 2 North Ana 1200 North Chloe 900 3 South Ben 800 South Ben 800 4 North Chloe 900 5 West Ana 400 6 North Ana 300 In E1:
=CHOOSEROWS(SORT(A2:C6, 3, -1), 1, 2, 3)→ North, Ana, 1200; North, Chloe, 900; South, Ben, 800
Common mistakes
- A row that is not there. Asking for row 10 of a range with 6 rows gives #VALUE!.
CHOOSEROWS in other languages
Excel shows function names in its own language. In these languages it also puts a semicolon between arguments where English has a comma.
- Danish: VÆLGRÆKKER
- Dutch: KIES.RIJEN
- German: ZEILENWAHL
- Italian: SCEGLI.RIGA
- Russian: ВЫБОРСТРОК
- Spanish: ELEGIRFILAS
- Swedish: VÄLJRADER
- Turkish: SATIRSEÇ
Try CHOOSEROWS in the playground
Related functions
- CHOOSECOLS: Returns the columns you name from an array, in the order you name them.
- TAKE: Keeps the first or last rows or columns of an array.
- DROP: Removes the first or last rows or columns of an array.
- INDEX: The value at a given row and column of a range.