How to Extract Matching Excel Rows Using Formulas or VBA
Question details
The user needs a method to cross-reference two columns (such as chassis numbers) and extract corresponding row data (such as License IDs) using automated methods like formulas or VBA.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Comparing values in two different columns to identify matches, then automatically pulling related data from adjacent columns into a new column or a separate worksheet.
- Observed behavior
- The user wants an automated, dynamic approach to pull related records across rows when specific column conditions are met, eliminating the need for manual row-by-row searching.
Ensure you are using a modern spreadsheet version (like Microsoft 365 or WPS Office) if you plan to use dynamic array formulas like FILTER. For older versions, the VBA method is the recommended fallback.
Use Dynamic Array Formulas (FILTER and TEXTJOIN)
Best for modern spreadsheet users who want to dynamically extract and combine matched data without writing code.
You can combine FILTER, UNIQUE, and TEXTJOIN to dynamically pull related data when two columns match. This approach is highly efficient because the extracted results update automatically whenever your source data changes.
Click on an empty cell where you want the extracted data to appear (for example, cell D2).
Type the formula: =TEXTJOIN("/",TRUE,UNIQUE(FILTER($B$2:$C$101,$A$2:$A$101=C2,""))) and press Enter.
Click the cell with the formula, grab the fill handle in the bottom-right corner, and drag it down to apply the formula to the rest of your dataset.
If you need the joined values in separate columns, select the results, go to the 'Data' tab, and use the 'Text to Columns' feature with '/' as your delimiter.
Use a VBA Macro to Transfer Matching Rows
Ideal for users who need to automate the process of copying entire matching rows to a completely different worksheet.
Extract and Filter Data Seamlessly with WPS Spreadsheet
WPS Spreadsheet provides comprehensive support for modern dynamic array formulas like FILTER, UNIQUE, and TEXTJOIN, making it effortless to cross-reference columns and extract matching records without complex setups.
- 1. Open your workbook: Launch WPS Spreadsheet and open the file containing the columns you need to match.
- 2. Use dynamic array formulas: Type your =FILTER(...) or =TEXTJOIN(...) formulas in an empty cell just as you would in Microsoft 365, and press Enter to fetch the matching rows instantly.
- 3. Manage your extracted data: Utilize the intuitive 'Data' ribbon in WPS Office to run 'Text to Columns' or manage your extracted lists with zero lag.
Frequently Asked Questions
What if my version of Excel doesn't support the FILTER function?
If you are using an older version (like Excel 2016 or 2019), dynamic array formulas like FILTER are not available. You will need to use alternative functions like INDEX and MATCH combined with IFERROR, or utilize the VBA method provided in the solutions.
Can I match values and extract data across two different worksheets?
Yes. In both the formula and VBA approaches, you can easily reference columns from a different worksheet. For formulas, just add the sheet name before the cell range, such as Sheet2!$A$2:$A$101.
How do I separate the extracted data if it's joined by a slash?
Highlight the column containing the joined text, go to the 'Data' tab, select 'Text to Columns', choose 'Delimited', and set the delimiter to 'Other' with a forward slash (/) to split the values into separate columns.
