CHOOSEROWS Function – Google Sheets

The CHOOSEROWS function in Google Sheets allows you to return specific rows from a given range or array. This function is helpful when you need to extract a subset of your data without manually copying and pasting or when building dynamic reports.

Syntax

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

• range: The data source from which you want to extract rows. It can be a cell range (e.g., A1:D10) or an array literal (e.g., {1,2,3; 4,5,6}).
• row_num1: This is the first row number to select from the range. It’s relative to the range itself, not the entire sheet. So, if your range starts at A5, row_num1 = 1 would refer to row A5.
• [row_num2, ...]: These are optional additional row numbers you want to select. You can specify as many as needed.

Common Errors

#VALUE! – The row_num is out of range. Its absolute value should be equal to or less than the number of rows in the range.

Similar Functions

CHOOSECOLS – Return specific columns from a range or array

Practical Examples

Let’s illustrate with some common scenarios and follow along with your copy of the Google Sheet with these examples. Imagine you have sales data in A2:C6 structured as follows:

Example 1: Selecting Specific Rows from a Data Table

To extract just Bob‘s and Eve‘s sales data, you would use:

=CHOOSEROWS(A2:C6, 2, 5)

Notice that we used A2:C instead of A1:C as the range because our headers are in row 1. The 2 refers to the second row of the chosen range (Bob), and the 5 refers to the fifth row (Eve).

This formula looks like this:

Example 2: Choosing Rows from the Bottom

You can also use CHOOSEROWS to pick rows starting at the bottom of a range. To display Eve and David‘s data, we need to specify -1 and -2, respectively, as they are 1st and 2nd from the bottom.

=CHOOSEROWS(A2:C6, -1, -2)

This formula in action looks like this:

As you can see, the CHOOSEROWS formula returned the last two rows.

Example 3: Dynamic Row Selection with Other Functions

CHOOSEROWS truly shines when combined with other functions. For instance, to get the last two rows of a dynamically growing dataset, you could use COUNTA to find the total number of rows:

=CHOOSEROWS(A2:C, COUNTA(A2:A)-1, COUNTA(A2:A))

This formula assumes that column A always has data, and A2:C means the range extends to the bottom of the spreadsheet grid. If there are 5 rows of data (including headers), COUNTA(A2:A) will return 5. COUNTA(A2:A)-1 gives the 4th data row (2nd to last), and COUNTA(A2:A) gives the 5th (last).

Video Tutorial

Conclusion

By understanding CHOOSEROWS, you gain a flexible way to manipulate and present your data more efficiently in Google Sheets.

Related Articles