Function Problems

How to Extract All Excel Rows Matching Multiple Values

Phi Hung Vo Sep 28, 2026 869 views

Question details

The user needs to extract every row in an Excel spreadsheet that matches multiple specific criteria, rather than just the first match.

How to Extract All Excel Rows Matching Multiple Values

Product: Microsoft Excel
Device & OS: not provided
Scenario: Extracting multiple rows of data from a large dataset based on a list of specific criteria values.
Observed behavior: Traditional INDEX and MATCH functions only return the first matching row, failing to output all rows that meet the specified conditions.

Before you start

Ensure your source data and criteria are listed in clearly defined, separate ranges. Note that using the FILTER function requires a version of Excel that supports dynamic arrays, such as Excel 365 or newer.

Solution 1Recommended

Extract Multiple Rows Using FILTER and MATCH Functions

Combine the FILTER and MATCH functions to evaluate your criteria and dynamically extract all rows that meet the multiple conditions.

The FILTER function is highly efficient for pulling multiple records, and nesting it with ISNUMBER and MATCH allows you to check against an array of criteria values simultaneously.

Select the destination cell

Click on an empty cell where you want the top-left corner of your extracted data to appear.

Input the base FILTER formula

Type the formula `=FILTER(HSTACK(Sheet1!A:A,Sheet1!D:D,Sheet1!H:H),ISNUMBER(MATCH(Sheet1!E:E,H5:H10,FALSE)))`. Use the HSTACK function to combine the specific non-adjacent columns you want to return (e.g., Columns A, D, and H).

Define the criteria range

Replace `Sheet1!E:E` with the column you are evaluating against, and replace `H5:H10` with the actual range containing your multiple criteria values.

Execute the formula

Press Enter. The formula will dynamically spill all the matching rows into the adjacent cells automatically.

Extract Multiple Rows Using FILTER and MATCH Functions

Dynamic Updates: Because this utilizes dynamic array formulas, your extracted data will instantly update if you add, remove, or change values within the criteria range.

Solution 2

Extract Data Using Power Query

Use Power Query to filter and extract rows matching multiple values without needing complex dynamic array formulas.

Easily Filter and Extract Data with WPS Spreadsheet

WPS Spreadsheet fully supports dynamic array functions like FILTER and MATCH, making it simple to extract matching rows. It provides a robust, free environment for complex data processing and analysis.

1. Open your dataset: Launch WPS Spreadsheet and open the file containing your source data and criteria.
2. Select the target cell: Click the empty cell where you want to output the extracted records.
3. Enter the formula: Type your `=FILTER(...)` formula, specifying your desired columns and matching criteria range.
4. Extract the rows: Press Enter to instantly generate and spill all matching rows.

Fully compatible with Microsoft Excel formulas and .xlsx files.Seamlessly supports dynamic array functions for complex data extraction.Lightweight, fast, and features a familiar, easy-to-navigate user interface.

microsoft office alternative - wps office

Frequently Asked Questions

Why is my INDEX and MATCH formula only returning one row?

By design, the MATCH function stops searching and returns the relative position of the very first match it encounters. To return multiple matches, you must use dynamic array functions like FILTER or legacy array formulas.

What does the HSTACK function do in the FILTER formula?

HSTACK combines multiple non-adjacent columns (like Column A, D, and H) horizontally into a single array. This allows the FILTER function to output data from separated columns without pulling in unwanted data in between.

What should I do if the FILTER formula returns a #CALC! error?

The #CALC! error usually occurs when the FILTER function finds no matching records. You can fix this by adding an optional third argument to the FILTER function, such as `=FILTER(range, condition, "No results")`, to display a custom text message instead of an error.

Can I extract matching rows without Excel 365?

Yes, if you do not have Excel 365, you can use Power Query to filter and load the data, or you can use complex legacy array formulas combining INDEX, SMALL, IF, and ROW functions.

Excel & Data Problems