When handling a large dataset, you might need to extract a portion of the data into a new range for further analysis, report generation, or printing purposes. If there are no specific criteria to filter the data, you can use row numbers as filter criteria in Google Sheets.

You may want to extract data from multiple portions of your dataset. In such cases, the formula may vary depending on how you specify the row numbers.

Example of Using Row Numbers as Filter Criteria in Google Sheets

Assume you have employee data in the range A1:E and you want to filter the records from rows 5 to 10. You can use the following formula:

=FILTER(A1:E, ISBETWEEN(ROW(A1:A), 5, 10))

The formula follows this syntax:

FILTER(range, condition1, [condition2, …])

• Range: A1:E
• Condition: ISBETWEEN(ROW(A1:A), 5, 10), which returns an array of TRUE or FALSE.

This formula filters the rows wherever the condition evaluates to TRUE.

The condition is actually an ISBETWEEN function, which works as follows:

ISBETWEEN(value_to_compare, lower_value, upper_value, [lower_value_is_inclusive], [upper_value_is_inclusive])

• value_to_compare: The row numbers in the sheet.
• lower_value: The starting row number to filter.
• upper_value: The ending row number to filter.

Important Notes:

• The condition and filter range must have the same number of rows.
• If your range is A10:X100, then the value_to_compare must be ROW(A10:A100).

How Do I Specify Multiple Sets of Row Numbers as the Filter Condition?

If you want to filter rows from row #5 to 10 and row #20 to 25, the formula should be:

=FILTER(A1:E, ISBETWEEN(ROW(A1:A), 5, 10) + ISBETWEEN(ROW(A1:A), 20, 25))

This formula uses the + operator to combine multiple conditions, allowing you to filter non-contiguous sets of rows.

Filtering Non-contiguous Rows in Google Sheets

The above approach may not work well when you need to filter non-contiguous rows.

For example, if you want to filter rows 5, 10, and 15, you can use the following FILTER formula:

=FILTER(A1:E, XMATCH(ROW(A1:A), {5, 10, 15}))

In this formula, we use XMATCH to match the row numbers in A1:A with the specified array {5, 10, 15}. XMATCH returns the relative position of the matching rows and #N/A for the rows that don’t match. The FILTER function then filters the rows wherever XMATCH returns a valid number (relative position).

This approach allows you to filter non-contiguous rows, making it a flexible solution for such scenarios.

Resources

• Find Row Numbers Where Values Change in Google Sheets
• Get the First and Last Row Numbers of Items in Google Sheets
• Formula to Conditionally Filter Last N Rows in Google Sheets
• How to Filter Next Row to the Filter Criteria Row in Google Sheets
• Filter Last Status Change Rows in Google Sheets
• Google Sheets: Filter Rows with Furthest Data in Each Category