Formula Errors

Excel Formula to Keep List B Rows That Match List A

Nimra Malik Sep 28, 2026 869 views

Question details

The user needs an Excel formula to compare two lists and extract rows from List B when specific column values match those in List A.

Excel Formula to Keep List B Rows That Match List A

Product: Microsoft Excel
Device & OS: not provided
Scenario: Comparing two datasets to dynamically extract full rows from one list based on matching column values in another list.
Observed behavior: When extracting data, large dynamic arrays may return a #CALC! error if no matches are found or if the referenced ranges are incompatible.

Before you start

Ensure that both List A and List B do not contain unintended blank cells or merged headers, as these can disrupt dynamic array formulas and cause calculation errors.

Solution 1Recommended

Use FILTER and XMATCH to Return Matching Rows

This is the most efficient dynamic array method to filter matching records while preventing #CALC! errors.

The FILTER function extracts rows based on a boolean array, while XMATCH quickly checks if a value from List B exists in List A. Wrapping XMATCH in ISNUMBER creates the TRUE/FALSE condition required by FILTER.

Select the destination cell

Click on the cell where you want the extracted list to begin (for example, a blank cell below your current data).

Enter the FILTER and XMATCH formula

Type the formula =FILTER(A8:G12, ISNUMBER(XMATCH(A8:A12, A1:A5)), "No matches") into the formula bar.

Adjust the data ranges

Replace A8:G12 with your entire List B data range, A8:A12 with the specific column in List B to compare, and A1:A5 with the matching column in List A.

Execute the formula

Press Enter. The matching rows from List B will automatically spill into the adjacent cells to complete the extracted list.

Use FILTER and XMATCH to Return Matching Rows

Preventing #CALC! Errors: Adding the "No matches" text as the third argument in the FILTER function safely handles scenarios where no records match, preventing the #CALC! error.

Solution 2

Use FILTER and XLOOKUP as an Alternative

An alternative approach if you prefer using XLOOKUP to identify non-empty matches between the two lists.

Seamlessly Compare and Filter Lists in WPS Office

WPS Spreadsheet fully supports dynamic array formulas like FILTER, XMATCH, and XLOOKUP, allowing you to easily extract data across lists without compatibility issues.

1. Open WPS Spreadsheet: Launch WPS Office and open your spreadsheet containing List A and List B.
2. Enter the Formula: Select a blank cell and input =FILTER(A8:G12, ISNUMBER(XMATCH(A8:A12, A1:A5)), "No matches").
3. Review the Results: Press Enter to spill the matched rows instantly, seamlessly handling any empty datasets without #CALC! errors.

Fully compatible with Microsoft Excel formulas and file formats (.xlsx)Supports dynamic arrays and advanced lookup operations seamlesslyLightweight application with fast processing for large datasets

microsoft office alternative - wps office

Frequently Asked Questions

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

The #CALC! error occurs when the FILTER function returns an empty array because no matches were found in your lists. You can fix this by adding a string like "No matches" as the third argument in the FILTER formula.

Can I compare lists across different worksheets?

Yes, you can reference ranges from different sheets. For example, your List A reference can look like Sheet1!A1:A5 while your formula resides on the same sheet as List B.

Does the XMATCH function work in older Excel versions?

XMATCH is a dynamic array function available in Microsoft 365, Excel 2021, and modern versions of WPS Office. For older versions, you can use the traditional MATCH function combined with ISNUMBER instead.

Why is the formula returning a #SPILL! error?

A #SPILL! error happens when the dynamic array needs to output the filtered rows, but the adjacent cells are not empty. Clear the cells below and to the right of your formula to allow the results to populate.

Excel & Data Problems