How to Use CHOOSEROWS Function in Google Sheets

This guide will explain how to use the CHOOSEROWS function in Google Sheets to return specified rows from a given array.

Google Sheets provides multiple ways to filter specific data from a given range. However, you may find it difficult to return a selection of rows from a range.

For example, given a specific range, how can we output the first, fifth, and last rows? Or perhaps you want to return the first three and last three items in the range.

Luckily, Google Sheets allows you to specify what rows to return in a range with the CHOOSEROWS function.

In this guide, we will provide a step-by-step tutorial on how to use the CHOOSEROWS function in Google Sheets. We will look into each argument of the function and later explore a real example of the function in action.

Let’s dive right in!

 

The Anatomy of the CHOOSEROWS Function

The syntax of the CHOOSEROWS function is as follows:

=CHOOSEROWS(array, row_num1, [row_num2])

Let’s look at each argument to understand how to use the CHOOSEROWS function.

  • = the equal sign is how we start any function in Google Sheets.
  • CHOOSEROWS() refers to our CHOOSEROWS function. This function outputs a new array given an existing range and a list of selected rows from that range.
  • array refers to the array that contains the rows you want to return.
  • row_num1 refers to the row number of the first row to return.
  • [row_num2] refers to the next row number to return in the final array output. 
  • Providing a negative row number will indicate that you want to get the nth row starting from the back. For example, -1 will return the last row in the array.
  • row_num arguments can also be provided as an array. 

A Real Example of the CHOOSEROWS Function in Google Sheets

Let’s explore a simple example where we may need to use the CHOOSEROWS function.

In the table below, we have a table containing student names and the score they received in the latest exam. The table is already sorted in descending order by score.

sampel range

We want to create a new range that outputs the rows of the three highest scorers.

CHOOSEROWS function in Google Sheets

We can output the range we need by using the following formula:

=CHOOSEROWS(A1:B7, 1, 2, 3, 4)

The first argument controls what range to retrieve the rows from. The next few parameters indicate which rows to include in the output. We’ve added the first row to include the column header. 

Afterward, we’ve added rows 2 to 4 since they correspond to the three highest earners.

If we want to return the last few rows, we can use negative numbers to refer to rows starting from the bottom.

CHOOSEROWS function in Google Sheets with negative numbers

In the example above, we used the value -1 to refer to the last row in the range A1:B7.
We can also use an array of values as a parameter. In the example below, we added the array {1, 2, 3, 4} as a parameter instead of adding four more arguments to the function.

CHOOSEROWS function in Google Sheets

Do you want to take a closer look at our examples? You can make your own copy of the spreadsheet above using the link below. 

View our sample spreadsheet to see multiple examples of the CHOOSEROWS function in action.

If you’re ready to try using the CHOOSEROWS function yourself, head over to the next section to read our step-by-step breakdown on how to do it!

 

How to Use the CHOOSEROWS Function in Google Sheets

This section will guide you through each step needed to use the CHOOSEROWS function in Google Sheets.

Follow these steps to start extracting specific rows from a range:

  1. First, select an empty cell to place the CHOOSEROWS function. Ensure that there is enough space around the cell to fit the number of rows you want to return.
    select blank cells
  2. Next, type the string “=CHOOSEROWS(“ to start the function.
    CHOOSEROWS function in Google Sheets
  3. For the function’s first argument, select the range you want to choose rows from.
    select rangeIn our example, we will use the range A1:B7.
  4. Next, add each row you want to output as a new argument.
    select rows to output as arguments
  5. Hit the Enter key to evaluate the function.
    CHOOSEROWS function in Google Sheets
  6. We can also use negative row numbers to indicate we want to count starting from the bottom row.
    use negative numbers for row number
  7. We can also use an array to choose the rows you want to output.
    CHOOSEROWS function in Google Sheets with array argument
     

Frequently Asked Questions (FAQ)

Here are some frequently asked questions about this topic:

  1. Why does my CHOOSEROWS function return an error?
    Google Sheets will return a #VALUE error if the user provides a row number of 0. It will also return a #VALUE error if the absolute value of any row_num argument exceeds the number of rows in the provided array.

 

 

This tutorial should cover everything you need to know to about the CHOOSEROWS function in Google Sheets.

We’ve explained how the function can help users extract specific rows from a given range. We’ve shown you several examples showcasing different use cases, such as choosing the range’s first or last row.

The CHOOSEROWS function is just one of many built-in functions available in Google Sheets. For example, another way we can extract data from a range is through the FILTER function. You can read our step-by-step guide on how to use this function in your spreadsheet.

You may also check our guide on how to use the QUERY function in Google Sheets to learn another function that can filter and sort your spreadsheet data.

That’s all for this guide on the CHOOSEROWS function! If you’re still looking to learn more about Google Sheets, be sure to check out our library of resources, tips, and tricks!

Get emails from us about Excel and Google Sheets.

Our goal this year is to create lots of rich, bite-sized tutorials for spreadsheet users like you. If you liked this one, you'd love what we are working on! Readers receive early access to new content.

0 Shares:
Leave a Reply

Your email address will not be published. Required fields are marked *

You May Also Like