Function Problems

How to Filter an Excel Worksheet Using IDs from Another Sheet

Camila Milosovich Oct 9, 2026 868 views

Question details

The user needs to extract specific rows of data into a separate block based on a list of unique IDs located in a different worksheet.

How to Filter an Excel Worksheet Using IDs from Another Sheet

Product: Spreadsheet
Device & OS: not provided
Scenario: Comparing two lists of data to extract matching records into a new, clean list without manual sorting.
Observed behavior: Using conditional formatting only visually highlights the rows containing the matching IDs, rather than extracting them as a separate, filterable block of data.

Before you start

Ensure both worksheets have a designated column for the unique IDs (such as Employee ID or Product Code) and verify that there are no hidden spaces in your ID cells.

Solution 1Recommended

Use the FILTER and XMATCH Dynamic Array Formula

The most efficient method to dynamically extract matching rows into a new location without altering the original data.

This approach utilizes modern dynamic array functions. The FILTER function extracts the rows, while XMATCH checks if the ID exists in the secondary sheet. As your data changes, the results update automatically.

Select a destination cell

Click on an empty cell in a new worksheet or a blank area where you want the extracted data to appear. Ensure there is enough empty space below and to the right for the data to populate.

Input the FILTER formula

Type the formula: =FILTER(Sheet2!A2:Z1000, ISNUMBER(XMATCH(Sheet2!A2:A1000, Sheet1!A2:A1000))) into the formula bar.

Adjust your data ranges

Modify 'Sheet2!A2:Z1000' to match your main data table. Change 'Sheet2!A2:A1000' to the column containing the IDs in your main table, and 'Sheet1!A2:A1000' to the column containing the lookup IDs in your other sheet.

Apply the formula

Press Enter. The formula will automatically spill the matching rows into the adjacent cells, creating a separate block of filtered data.

Use the FILTER and XMATCH Dynamic Array Formula

Automatic Updates: If you add or remove IDs from your lookup list in Sheet1, the filtered results will instantly update to reflect the changes.

Solution 2

Use a Helper Column with COUNTIF

A reliable alternative for older spreadsheet versions that do not support dynamic array formulas like FILTER.

Advanced Data Filtering

Filter and Extract Data Easily with WPS Spreadsheet

WPS Office fully supports advanced dynamic array formulas like FILTER and XMATCH, allowing you to seamlessly cross-reference and extract data across multiple worksheets.

1. Open your workbook: Launch WPS Spreadsheet and open the workbook containing your separate data lists.
2. Navigate to a blank sheet: Create or select a new, empty worksheet where you want the filtered data to be securely placed.
3. Apply the formula: Use the =FILTER() function combined with XMATCH to instantly pull your matching records from the source sheet.
4. Save and share: Save your document in the standard .xlsx format to ensure seamless sharing and compatibility with other users.

Fully compatible with Microsoft Excel formulas and .xlsx file formats.Handles large datasets efficiently without lagging or crashing.Includes built-in advanced filtering and modern dynamic array functions.Free and lightweight alternative for complex data analysis tasks.

microsoft office alternative - wps office

Frequently Asked Questions

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

This error typically occurs when the FILTER function finds no records matching your criteria. You can prevent this by adding a third argument to the formula, such as =FILTER(range, criteria, "No matches"), to display a custom text instead of an error.

Can I use VLOOKUP instead of FILTER for this task?

VLOOKUP can pull in specific columns of data based on a single ID, but it does not filter or extract entire rows dynamically into a new list. The FILTER function or the helper column method is much better suited for extracting complete data records.

Why can't I just use conditional formatting to extract the data?

Conditional formatting is designed exclusively to apply visual styles, such as background colors or bold text, to cells that meet specific criteria. It cannot physically separate, move, or extract the data into a new block. To do that, you must use a formula or a standard filter.

Excel & Data Problems