Power Query Problems

How to Combine Excel Tables with Multiple Matching Values

Maira Mehtab Sep 22, 2026 868 views

Question details

The user needs to combine multiple data tables (such as maker, product, buyer, and product-code lists) to extract every valid maker-buyer combination for shared product codes, rather than just the first match.

Product: Excel
Device & OS: not provided
Scenario: Merging and comparing relational data across multiple tables sharing common identifier columns.
Observed behavior: Instead of retrieving only the first matched record like standard lookup functions do, the user wants a consolidated result table outputting all matching combinations across the source tables.

Before you start

Ensure your source tables are formatted as official Excel Tables (press Ctrl+T) and clearly verify that your shared key columns, such as 'Product Code', have identical data types to guarantee accurate matching.

Solution 1Recommended

Use Power Query to Merge Tables and Expand Multiple Matches

Power Query is the most robust and scalable tool for joining tables and expanding multiple matching rows without writing complex nested formulas.

When merging queries, Power Query inherently captures all relational matches. By expanding the merged column, it automatically duplicates the primary table's rows for every corresponding match found in the secondary table.

Load Tables to Power Query

Select your first table, navigate to the Data tab, and click 'From Table/Range' to load it into the Power Query Editor. Choose 'Close & Load To...' -> 'Only Create Connection'. Repeat this process for your secondary table.

Initiate Merge Queries

In the Power Query Editor, go to the Home ribbon and click 'Merge Queries' (or 'Merge Queries as New' to keep original tables intact).

Configure the Join

Select your primary table from the top drop-down and your secondary table from the bottom drop-down. Click on the matching columns (e.g., Product Code) in both preview panes. Leave the Join Kind as 'Left Outer' and click OK.

Expand the Matched Tables

A new column containing 'Table' values will appear. Click the Expand icon (two diverging arrows) in the column header, select the columns you want to bring in (like Maker or Buyer), uncheck 'Use original column name as prefix', and click OK.

Load the Result

Click 'Close & Load' on the Home tab to output the newly expanded dataset, which now displays all multiple matching combinations, into a new worksheet.

Data Refresh: If you add new buyers or product codes to your original tables later, simply right-click your merged output table and select 'Refresh' to update the combinations automatically.

Solution 2

Use the FILTER Function for Multi-Match Lookups

For users with modern Excel versions, dynamic array functions like FILTER provide a quick formula-based approach to retrieve all matches.

Easily Process Multi-Match Tables in WPS Spreadsheet

WPS Spreadsheet provides powerful data management tools and supports advanced array functions, allowing you to quickly merge datasets, analyze shared product codes, and extract multiple matching values efficiently.

1. Open Your Data Workbook: Launch WPS Spreadsheet and open your .xlsx workbook containing the multiple tables (maker, product, buyer).
2. Apply Array Functions: Utilize built-in functions like =FILTER() and =TEXTJOIN() to pull all matching rows for your specific criteria instantly.
3. Use Pivot or Consolidation Tools: Navigate to the Data tab to utilize consolidation features, making it simple to visually map out relationships and summarize shared product code combinations.

Fully compatible with Microsoft Excel file formats (.xlsx)Supports dynamic array functions like FILTER for retrieving multiple matchesUser-friendly interface for managing complex data relationships and pivot tablesLightweight architecture ensures smooth performance with large data tables

Frequently Asked Questions

Why does VLOOKUP only return the first matching value from my table?

By design, standard lookup functions like VLOOKUP and INDEX/MATCH scan the target column from top to bottom and stop executing as soon as they locate the first exact match. To return multiple matches, you must bypass VLOOKUP and use Power Query or dynamic array functions like FILTER.

Can I combine multiple matching values into one single cell separated by commas?

Yes. You can nest the FILTER function inside a TEXTJOIN function. The formula structure =TEXTJOIN(", ", TRUE, FILTER(Return_Range, Criteria_Range=Criteria)) will condense all found matches into a single string within one cell.

What should I do if Power Query is not available on my device?

If you are using an older version of Excel or Excel for Mac where Power Query features might be limited, you can rely on the FILTER function if your version supports dynamic arrays, or use complex INDEX/AGGREGATE array formulas. Alternatively, utilizing pivot tables can help summarize multiple matches under a single product row.

Excel & Data Problems