Function Problems

How to Use an Excel Formula to Keep Rows Matching Another List

Maira Mehtab Sep 22, 2026 869 views

Question details

The user needs to compare two lists of data in a spreadsheet and automatically extract only the rows from the second list that contain values present in the first list.

Product: Excel
Device & OS: not provided
Scenario: Filtering a dataset dynamically to keep rows that match criteria from another reference list without manually deleting non-matching rows.
Observed behavior: Requires a dynamic array formula solution to return intersecting rows from List B based on reference values in List A.

Before you start

Ensure you are using a spreadsheet application that supports modern Dynamic Array functions, such as FILTER, XLOOKUP, and XMATCH.

Solution 1Recommended

Use FILTER and XMATCH to Return Matching Rows

Combining the FILTER and XMATCH functions is the most efficient method to extract rows matching another list.

This approach leverages dynamic arrays to spill the results automatically, eliminating the need to manually delete unmatched rows.

To prevent performance issues and #CALC! errors on large datasets, it is highly recommended to use bounded ranges or convert your data into Excel Tables rather than referencing entire columns.

Select the destination cell

Click on an empty cell where you want the top-left corner of your newly filtered list to appear.

Enter the FILTER and XMATCH formula

Type the formula =FILTER(A8:G12, ISNUMBER(XMATCH(A8:A12, A1:A5))). In this syntax, A8:G12 is your main dataset, A8:A12 is the column you are checking, and A1:A5 is your reference list.

Execute the formula

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

Avoid #CALC! Errors: If you encounter a #CALC! error, check that your array dimensions are compatible and ensure you aren't referencing entire columns (e.g., A:A) if the dataset is massive.

Solution 2

Use FILTER and XLOOKUP as an Alternative

Another effective approach for extracting matching records is pairing the FILTER function with XLOOKUP.

Advanced Spreadsheets with WPS Office

Easily Filter and Compare Lists with WPS Spreadsheet

WPS Spreadsheet fully supports advanced dynamic array functions like FILTER, XLOOKUP, and XMATCH, making it incredibly easy to compare lists and extract matching rows dynamically.

1. Open your workbook: Launch WPS Spreadsheet and open the file containing the two lists you want to compare.
2. Apply the formula: Select a blank cell and input =FILTER(A8:G12, ISNUMBER(XMATCH(A8:A12, A1:A5))) adjusting the ranges for your data.
3. Press Enter: Hit Enter to instantly generate and spill the filtered list of matching rows.

Fully compatible with Microsoft Excel formulas and .xlsx file formats.Seamlessly supports dynamic arrays and modern functions like FILTER and XLOOKUP.Lightweight architecture ensures smooth performance even with large datasets.Free to use with a familiar, user-friendly interface that requires zero learning curve.

Frequently Asked Questions

Why does my FILTER formula return a #CALC! error?

A #CALC! error typically occurs when the FILTER function finds no matching records (resulting in an empty array), or when the array dimensions referenced in the formula are incompatible. Extremely large, unbounded ranges can also trigger this error.

Can I use VLOOKUP instead of XLOOKUP for this task?

While VLOOKUP can be used to compare lists in standard scenarios, combining it with FILTER to return an array of rows is more complex and less efficient than using modern dynamic array functions like XMATCH or XLOOKUP.

What if my software doesn't support the FILTER function?

If you are using an older spreadsheet version without dynamic array support, you can use the Advanced Filter tool from the Data tab, or upgrade to a modern, free alternative like WPS Office to access modern functions.

How can I account for empty cells in my reference list?

Empty cells can cause false positives or mismatch issues. It is best to clean your data first to remove blanks, or wrap your reference array in a function like FILTER to exclude blanks before matching.

Excel & Data Problems