Function Problems

How to Use Excel FILTER to Pull Matching Rows from Another Worksheet

Huma Ashraf Ch Oct 9, 2026 868 views

Question details

The user needs to create a spreadsheet template that automatically retrieves all rows matching a specific criteria (like an employee's name) from a master worksheet.

Excel FILTER Function in WPS Spreadsheet: Syntax, Examples, and Fixes

Product: Spreadsheet
Device & OS: not provided
Scenario: Pulling multiple related entries, such as varying infraction dates and types for a single employee, from a raw data sheet into a formatted template.
Observed behavior: The goal is to use a dynamic formula to return multiple rows of matching data at once, replacing manual filtering or copying and pasting.

Before you start

Verify that you are using a spreadsheet version that supports dynamic array functions. The FILTER function is available in Microsoft 365, Excel 2021, and the latest versions of WPS Office.

Solution 1Recommended

Apply the FILTER Function to Extract Matching Rows

Use a dynamic array formula to instantly search the source worksheet and spill all matching records into your new template.

The FILTER function allows you to specify a range of data, define a logical test for inclusion, and optionally set a message if no records match. Because it is a dynamic array function, it will automatically "spill" the results into the adjacent cells, eliminating the need to drag the formula down.

Select your destination cell

Navigate to the template worksheet and click the top-left cell where you want the extracted data to begin appearing.

Enter the FILTER formula

Type =FILTER(Sheet1!B:C, Sheet1!A:A=$A$2, "No matches"). In this formula, Sheet1!B:C represents the columns of data (e.g., dates and infraction types) that you want to bring over.

Define the lookup criteria

The second part of the formula, Sheet1!A:A=$A$2, acts as the filter condition. It checks column A in the source sheet against the specific employee name typed into cell A2 of your template.

Execute the function

Press Enter. The formula will instantly pull every row that matches the employee's name and display the corresponding data down the columns.

Avoid #SPILL! Errors: Ensure there is enough empty space below and to the right of your formula cell. If any existing text or data blocks the path of the incoming rows, the formula will return a #SPILL! error.

Streamline Data with WPS Spreadsheet

Use WPS Office to Easily Filter and Extract Data

WPS Spreadsheet fully supports advanced dynamic array functions, allowing you to use formulas like FILTER seamlessly. It is an excellent tool for building automated data templates without any complex VBA programming.

1. Open your workbook: Launch WPS Spreadsheet and open the document containing your master data and template sheet.
2. Input the FILTER formula: Click the target cell in your template and enter the =FILTER() formula, referencing your source data and criteria cell.
3. Press Enter to spill data: Hit Enter to execute the formula and watch as all matching rows dynamically populate your template.

Fully compatible with Microsoft Excel formats (.xlsx, .xls)Supports modern dynamic array formulas like FILTER, UNIQUE, and SORTLightweight, fast-loading, and completely free to use

microsoft office alternative - wps office

Frequently Asked Questions

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

A #CALC! error usually appears when the FILTER function finds zero matching rows and the optional third argument [if_empty] was left blank. You can prevent this by adding a fallback message like "No matches" at the end of the formula: =FILTER(A:B, C:C=D1, "No matches").

Can I use the FILTER function with multiple criteria?

Yes. To require multiple conditions to be met (AND logic), you can multiply the criteria arrays together in the formula. For example: =FILTER(A:C, (B:B="Condition 1") * (C:C="Condition 2")).

Does the FILTER function work on older versions of Excel?

No, FILTER is a dynamic array function and is not supported in Excel 2019, 2016, or older versions. In those older versions, it will result in a #NAME? error. To use dynamic arrays, you need Microsoft 365, Excel 2021, or a modern alternative like WPS Office.

Excel & Data Problems