How to Match Two Excel Columns and Expand Duplicate Matches
Question details
The user needs to match two large Excel lists where one value in the first list corresponds to multiple values in the second list, requiring the first list to expand so each duplicate match has its own row.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Comparing two large lists and expanding rows to align multiple matches from the second list with the corresponding value in the first list without losing data.
- Observed behavior
- Standard lookup functions only return the first match. The goal is to return all matching values by expanding the original list rows automatically instead of manually inserting rows.
Ensure both of your lists are formatted as Excel Tables (Ctrl + T) to make data manipulation easier and prevent data loss when merging.
Use Power Query to Merge and Expand Matches
Power Query is the most reliable method for handling large datasets (e.g., 10,000+ rows) efficiently without manual copying or complex coding.
This approach uses a Left Outer Join to merge the two tables. It groups all matching values from the second list into a nested table, which is then expanded so every duplicate match gets its own row while leaving unmatched rows visible.
Select your first table, go to the Data tab, and click 'From Table/Range'. Repeat this process for your second table so both are loaded into the Power Query Editor.
In Power Query, select 'Merge Queries' from the Home tab. Choose Table1 as your primary table and Table2 as the secondary table. Click the matching columns in both previews and select 'Left Outer Join' as the Join Kind.
A new column containing nested tables will appear. Click the expand icon (two diverging arrows) on the column header. This will expand the matches into separate rows and repeat the corresponding first-list value automatically.
If you need specific list grouping, open the 'Advanced Editor' from the Home tab and input custom Table.NestedJoin and Table.AddColumn functions to further manipulate empty lists and repeated values.
Once the preview looks correct, click 'Close & Load' on the Home tab to output the newly expanded dataset into a new worksheet.
Apply a VBA Macro to Shift and Align Duplicate Matches
A VBA macro automates the process of inserting new rows to align matches directly in the worksheet.
Use Excel 365 Dynamic Array Functions
If you have an updated version of Excel 365, you can use modern array functions like MAP, LAMBDA, and FILTER to stack and map matches dynamically.
Need a Lightweight Alternative for Complex Spreadsheets?
If you find Microsoft Excel's Power Query and custom LAMBDA functions overly complex, resource-heavy, or expensive, consider WPS Office. It provides powerful data analysis tools, excellent format compatibility, and a highly intuitive interface—completely free.
Frequently Asked Questions
Why does VLOOKUP only return the first match?
VLOOKUP is designed by default to stop searching the dataset as soon as it finds the first exact or approximate match. To return multiple matches, you must use Power Query or advanced dynamic array functions like FILTER.
Can I match columns and keep duplicates without using Power Query?
Yes. If you use Excel 365, you can utilize dynamic array functions like FILTER combined with HSTACK, or you can run a custom VBA macro to loop through your data and dynamically insert blank rows when duplicate values are detected.
What is a Left Outer Join in Power Query?
A Left Outer Join is a merging method that takes all rows from your first (primary) table and matches them with any corresponding rows from your second table. If there are multiple matches in the second table, expanding the merged column creates new rows to accommodate every duplicate.
