Function Problems

How to Find Multiple Matches and Return Complete Rows in Excel

Maira Mehtab Oct 23, 2026 869 views

Question details

The user needs to search across multiple worksheets based on multiple criteria in specific columns (A, C, or F) and extract the entire matching rows.

How to Find Multiple Matches and Return Complete Rows in Excel

Product: Microsoft Excel
Device & OS: not provided
Scenario: Consolidating and filtering data from multiple vendor worksheets (approximately 250 rows total) based on complex search criteria to extract full records.
Observed behavior: The user wants to dynamically display every complete row that matches the given criteria across the different worksheets, rather than just returning a single matched value.

Before you start

Before applying formulas, ensure that all worksheets have identical column structures and headers. This makes combining data and applying search criteria much easier and prevents data mismatch errors.

Solution 1Recommended

Combine Worksheets and Use the Dynamic FILTER Function

The most straightforward method is to consolidate your data into a single master sheet and use the dynamic array FILTER function to extract all matching rows.

In modern versions of Excel, the FILTER function is the ideal tool for returning multiple matches. When dealing with multiple worksheets, the best practice is to first append the data into one continuous range.

Combine Data into a Master Sheet

Create a new worksheet. Copy the data ranges from all four vendor worksheets and paste them sequentially into this new master sheet, ensuring the columns align correctly.

Set Up Criteria Cells

Designate specific cells above your results area (e.g., H1, I1, J1) to input the specific search values you want to match against columns A, C, and F.

Apply the FILTER Formula

Click the top-left cell of your intended result area and enter the formula: =FILTER(A2:F250, (A2:A250=H1) + (C2:C250=I1) + (F2:F250=J1), "No matches found").

Generate Results

Press Enter. The formula will automatically spill all complete rows that match any of your criteria into the adjacent cells.

Combine Worksheets and Use the Dynamic FILTER Function

Using AND vs OR Logic: In the FILTER function, using the plus sign (+) creates OR criteria (matches in A OR C OR F). If you need the row to match ALL criteria simultaneously, use the asterisk (*) for AND logic instead.

Solution 2

Automate Data Consolidation and Filtering with Power Query

For a more robust solution that automatically updates when vendor data changes, use Power Query to append the sheets and filter the results without manual copying.

WPS Spreadsheet Solution

Easily Filter and Combine Data with WPS Spreadsheet

WPS Spreadsheet offers powerful dynamic array functions like FILTER, alongside intuitive data management tools. You can seamlessly manage multiple worksheets, extract matching rows, and handle complex datasets with full compatibility with Microsoft Excel formats.

1. Open Your Workbook: Launch WPS Spreadsheet and open the workbook containing your multiple vendor sheets.
2. Consolidate the Data: Create a new sheet and consolidate your data using copy-paste or the built-in consolidation tools.
3. Select the Target Cell: Click on the cell where you want the extracted matching rows to begin displaying.
4. Enter the FILTER Formula: Type the =FILTER() function, select your combined data array, define your multi-column criteria, and press Enter to instantly extract the complete rows.

Fully compatible with Microsoft Excel (.xlsx) files and advanced dynamic array formulas.Intuitive interface for filtering and sorting large datasets across multiple sheets.Free, lightweight, and fast performance even with complex multi-criteria searches.Built-in advanced filtering options for streamlined data consolidation.

microsoft office alternative - wps office

Frequently Asked Questions

Can I use VLOOKUP to return multiple matches in Excel?

No, a standard VLOOKUP is designed to return only the first match it finds. To return multiple matches and complete rows, you must use the FILTER function, or an advanced INDEX and AGGREGATE combination if you are using an older version of Excel.

How do I filter across multiple sheets without combining them first?

Filtering directly across multiple sheets is complex. In newer versions of Excel, you can nest the VSTACK function inside the FILTER function (e.g., =FILTER(VSTACK(Sheet1:Sheet4!A2:F100), ...)) to stack the data dynamically within the formula itself without physically copying the cells.

Why is my FILTER function returning a #CALC! error?

The #CALC! error typically occurs when the FILTER function finds zero matches for your criteria, and the optional 'if_empty' argument has not been provided. You can fix this by appending a string at the end of your formula: =FILTER(array, include, "No matches found").

Can I use wildcards with the FILTER function to find partial matches?

The FILTER function does not support traditional wildcards like the asterisk (*) directly in its equality checks. To filter by partial text, you need to nest the ISNUMBER and SEARCH functions within the include argument, such as ISNUMBER(SEARCH("keyword", A2:A100)).

Excel & Data Problems