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

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

  1. The first and last items

    ABC
    1ItemPen
    2PenRuler
    3Stapler
    4Folder
    5Tape
    6Glue
    7Ruler

    In C1: =CHOOSEROWS(A2:A7, 1, -1) → Pen; Ruler

    Try this example

  2. The top three sales, largest first

    ABCDEFG
    1RegionRepSalesNorthAna1200
    2NorthAna1200NorthChloe900
    3SouthBen800SouthBen800
    4NorthChloe900
    5WestAna400
    6NorthAna300

    In E1: =CHOOSEROWS(SORT(A2:C6, 3, -1), 1, 2, 3) → North, Ana, 1200; North, Chloe, 900; South, Ben, 800

    Try this example

Common mistakes

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.

Translate a whole formula

Try CHOOSEROWS in the playground

Related functions