How to Extract Rows from One Excel Sheet That Match Another List
Question details
The user needs a method to pull specific rows from a large dataset in one worksheet based on a shorter list of key values located in another worksheet.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Comparing two lists across different sheets to filter out and keep only the records in the main dataset that match the values provided in a secondary lookup list.
- Observed behavior
- The goal is to automatically return a filtered subset of complete rows from Sheet A that have matching key values in Sheet B, bypassing the need for manual row-by-row checks.
Verify that your version of Excel supports Dynamic Array formulas, as the most efficient method utilizes the FILTER and XMATCH functions available in Microsoft 365, Excel 2021, or newer WPS Office versions.
Use the FILTER and XMATCH Functions
This is the most efficient method for Microsoft 365 and Excel 2021 users, as it dynamically extracts matching rows into a new location without altering the original data.
By combining the FILTER function with ISNUMBER and XMATCH, you can instantly pull complete rows from your main dataset based on a list of criteria in another sheet. This method eliminates the need for manual filtering or adding helper columns.
Locate the main dataset in Sheet A (e.g., range A2:J5000) and the specific column you want to match against (e.g., Column J). Then, identify your lookup list in Sheet B (e.g., J2:J66).
Click on an empty cell in a new sheet or a blank area where you want the extracted rows to spill.
Type the formula: =FILTER(SheetA!A2:J5000,ISNUMBER(XMATCH(SheetA!J2:J5000,SheetB!J2:J66)))
Press Enter. The formula will automatically spill all rows from Sheet A where the value in Column J matches any value found in the list on Sheet B.
Use a VLOOKUP Helper Column with Data Filter
A reliable and widely compatible approach for older versions of Excel that do not support dynamic array functions.
Easily Filter and Extract Matching Data with WPS Spreadsheet
WPS Office Spreadsheet fully supports modern dynamic array formulas like FILTER, XMATCH, and UNIQUE. It provides a seamless way to extract matching data across multiple worksheets effortlessly.
- 1. Open your workbook: Launch WPS Office and open your spreadsheet file containing the two lists.
- 2. Select a blank cell: Click the top-left cell of the blank area where you want the extracted data to appear.
- 3. Apply the FILTER formula: Enter the dynamic FILTER and XMATCH formula tailored to your data ranges.
- 4. Extract instantly: Press Enter to let WPS Spreadsheet automatically calculate and spill the matched rows instantly.
Frequently Asked Questions
Can I use MATCH instead of XMATCH?
Yes, you can substitute XMATCH with the traditional MATCH function. The formula would look like: =FILTER(SheetA!A2:J5000, ISNUMBER(MATCH(SheetA!J2:J5000, SheetB!J2:J66, 0))). XMATCH is simply preferred in newer versions because it defaults to an exact match.
What if my Excel version doesn't support the FILTER function?
If you are using Excel 2019 or earlier, the FILTER function is not available. You can instead use the Advanced Filter feature found under the Data tab, or create a helper column with VLOOKUP/MATCH to identify and filter the matching records manually.
How do I extract rows that DO NOT match the second list?
To extract the non-matching rows, change the ISNUMBER condition to ISNA in your formula: =FILTER(SheetA!A2:J5000, ISNA(XMATCH(SheetA!J2:J5000, SheetB!J2:J66))). This will return all records from Sheet A that are missing from Sheet B.
Will formatting be preserved when extracting rows using formulas?
No, formula-based extractions like FILTER only return the values. Cell formatting, such as background colors or bold text from the original dataset, will not be carried over to the destination cells. You will need to reapply formatting manually.
