How to use CHOOSEROWS function in Excel?

The `CHOOSEROWS` function in Excel is used to select specific rows from an array or range. This function is particularly useful when you need to extract rows based on certain conditions or criteria.

Here’s how you can use the `CHOOSEROWS` function:

Syntax

=CHOOSEROWS(array, row_num1, [row_num2], ...)

array: This is the range or array from which you want to choose rows.
row_num1, row_num2, …: These are the row numbers within the specified array that you want to return. You can specify one or more row numbers.

Example

Suppose you have a data set in range `A1:C5` and you want to extract certain rows from it. Let’s say your data looks like this:

A1: Name  B1: Age  C1: City
A2: John  B2: 25   C2: New York
A3: Jane  B3: 30   C3: Los Angeles
A4: Bob   B4: 22   C4: Chicago
A5: Alice B5: 28   C5: Miami

If you want to choose rows 2 and 4 (which are John and Bob), you would use the `CHOOSEROWS` function like this:

=CHOOSEROWS(A1:C5, 2, 4)

Important Points

Sequential Order: You don’t need to list row numbers sequentially; you can specify any order, and the function will return the rows in that order.
Error Handling: If you specify a row number that does not exist within the array, `CHOOSEROWS` will return an error (`#VALUE!`).
Dynamic Arrays: `CHOOSEROWS` is part of Excel’s dynamic array functions, meaning it can return an array of rows without needing to confirm the formula with `Ctrl+Shift+Enter`.
Availability: Ensure you’re using a version of Excel that supports dynamic arrays, as `CHOOSEROWS` is not available in older versions such as Excel 2016 or 2019.

The `CHOOSEROWS` function can greatly simplify data manipulation by allowing you to efficiently extract specific rows you’re interested in for further analysis or reporting.

facebooklinkedin