How to Use the CHOOSEROWS and CHOOSECOLS Functions in Google Sheets to Extract Data

Manipulating large tabular datasets in Google Sheets often involves extracting specific columns or reordering select rows into a clean summary table. In the past, achieving this required complex combinations of QUERY, INDEX, or nested array formulas with curly brackets. Google Sheets offers two dynamic array functions—CHOOSEROWS and CHOOSECOLS—that simplify retrieving exact rows and columns by their numerical indices.

Syntax and Behaviour of CHOOSECOLS and CHOOSEROWS

Both functions follow an identical, straightforward syntax structure:

=CHOOSECOLS(array, col_num1, [col_num2, ...])
=CHOOSEROWS(array, row_num1, [row_num2, ...])

• array (required): The source table or cell range containing the data you wish to sample.
• col_num / row_num (required): An integer representing the column or row index within the specified range (starting at 1 for the first column or row).
• Negative Indices: A powerful capability of both functions is supporting negative integers to index from the end backwards. For example, -1 returns the last column or row, and -2 returns the penultimate entry.

Extracting and Reordering Columns with CHOOSECOLS

Suppose your source data resides in range A2:F100, where column A contains Employee ID, B is First Name, C is Last Name, D is Department, E is Start Date, and F is Salary. To build a roster showing only the Full Name (B and C) and Department (D), enter:

=CHOOSECOLS(A2:F100, 2, 3, 4)

The formula dynamically spills a three-column table across your worksheet. You can also reorder columns effortlessly; for instance, placing Department first followed by Last Name:

=CHOOSECOLS(A2:F100, 4, 3)

To extract the first column and the very last column (Salary) without counting intermediate columns, use negative indexing:

=CHOOSECOLS(A2:F100, 1, -1)

Filtering Specific Rows with CHOOSEROWS

When you need to pull specific milestone rows from a large financial log—such as the top row, the midpoint, and the final recorded row—CHOOSEROWS isolates them instantly:

=CHOOSEROWS(A2:F100, 1, 50, -1)

This construction outputs a three-row summary table containing row 1, row 50, and the final row of the dataset.

Combining CHOOSEROWS and CHOOSECOLS with SORT and FILTER

The true flexibility of these functions appears when chaining them with other Google Sheets calculation engines:

• Extracting Top 5 Performers: Pair CHOOSEROWS with SORT to isolate the five highest sales records:

  =CHOOSEROWS(SORT(A2:E50, 5, FALSE), 1, 2, 3, 4, 5)

• Combining Columns from Filtered Results: Wrap CHOOSECOLS around a FILTER formula to display only necessary attributes from matching records:

  =CHOOSECOLS(FILTER(A2:E50, D2:D50="Marketing"), 2, 5)

• Two-Dimensional Subsets: Nest both functions together to extract specific rows and columns simultaneously from a master matrix:

  =CHOOSECOLS(CHOOSEROWS(A2:Z100, 1, 10, 20), 1, 3, 5)

Common Errors and Troubleshooting

When working with numerical index formulas, keep these guidelines in mind:

• #VALUE! Error: Occurs if an index argument is zero (0). Indices must be positive (1, 2, 3…) or negative (-1, -2, -3…).
• #REF! Error: Occurs if you request a column or row number that exceeds the boundaries of the source array (for example, asking for column 7 in an array that only spans columns A through E).
• Spill Collisions: If destination cells to the right or below the formula contain existing data or text notes, Google Sheets returns a #REF! Spill range not clear message. Clear the blocking cells to allow the dynamic table to populate.