Merge Tables Like a Pro: Power Query vs Traditional VLOOKUP Methods

Image by Editor
 

Merging data from multiple tables is one of the most common tasks in data analysis. Whether combining customer information with sales data or integrating inventory records with supplier details, efficiently merging tables is essential. Many users rely on VLOOKUP, but Power Query provides a more scalable and efficient way to handle large datasets. In this tutorial, we’ll show how to merge tables like a pro using traditional VLOOKUP and Power Query tools while comparing two approaches.

Why Merge Tables:

You often work with multiple tables that contain related information. Let’s consider you have sales and product data and you want to merge these tables to create a comprehensive sales report that includes product details.

• Sales Data (Table 1): Contains order details.
• Product Details (Table 2): Contains product information.

What is VLOOKUP?

VLOOKUP (Vertical Lookup) is a function in Excel that searches for a value in the first column of a table and returns a value in the same row from a column you specify.

Syntax

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

• lookup_value: The value to search in the first column of the table.
• table_array: The range of cells that contains the data.
• col_index_num: The column number in the table from which to retrieve the value.
• range_lookup: TRUE for an approximate match, FALSE for an exact match (recommended).

What is Power Query?

Power Query is a data transformation and cleansing tool available in Excel and Power BI. It allows you to connect to various data sources, transform data, and create relationships between tables.

VLOOKUP: Traditional Method

Let’s merge the Product Name, Category, and Unit Price into our Sales Data.

• You must ensure both tables have a common column (e.g., Product ID).
• Open your Sales Data sheet and add three new columns: Product Name, Category, and Unit Price.
• In the Product Name column >> select cell F2 >> insert the following formula.

Formula:

=VLOOKUP(B2, 'Product Details'!A:E, 2, FALSE)

This formula searches Product ID in the product details sheet and returns the Product Name.

• B2: Lookup value (Product ID in Sales table).
• Product Details’!A:E: Lookup table range.
• 2: Column index (Product ID is in the 3rd column).
• FALSE: Exact match.

Now, insert the VLOOKUP formula into the rest of the columns by changing the column index number.

• In the Category column >> select cell G2 >> insert the following formula.

=VLOOKUP(B2, 'Product Details'!A:E, 3, FALSE)

• In the Unit Price column >> select cell H2 >> insert the following formula.

=VLOOKUP(B2, 'Product Details'!A:E, 4, FALSE)

Output:

Limitations of VLOOKUP with This Dataset

If we wanted to add Stock Level, we’d need another VLOOKUP formula. If the Product Details table changes the structure (e.g., a column is added), we’d need to update all our formulas. If a Product ID in Sales Data doesn’t exist in the Product table, we’ll get #N/A errors.

• Slower for large datasets (thousands of rows).
• Column index limitation (breaks if columns are reordered).
• Only works for left-to-right lookups (INDEX-MATCH can solve this).
• Requires manual updates when new data is added.

Power Query: The Modern Approach

Let’s merge the same tables using Power Query.

Load Data into Power Query:

• Select any cell in your Sales Data table.
• Go to the Data tab >> from Get Data >> select From Table/Range.

• Repeat for the Product Details table.

Merge Queries:

• Power Query Editor will open with your data.
• Go to the Home tab >> click Merge Queries.
• In the dialog box:
  • Select your Product Details table which is Table 2.
  • Select Product ID in both tables (click on the column headers).
  • Choose the Left Outer join type (to keep all sales records).
• Click OK.

Expand the Merged Data:

• In the new column (named Product Details), click the expanding button (double arrows).
• Click the expand icon () next to the merged column.
• Select Product Name, Category, Unit Price, and Stock Level.
• Uncheck Use the original column name as prefix.
• Click OK.

Load Data Back to Excel:

• Click Close & Load to apply changes.

The result will be a comprehensive sales report with all the product information merged in one smooth operation.

Limitations of Power Query:

• Learning curve for new users.
• Does not update instantly like formulas (you need to refresh the query).

Types of Joins in Power Query

To illustrate different join types using our dataset:

• Inner Join: This would only include orders where the Product ID exists in both tables (all rows in our example).
• Left Outer Join: Includes all sales orders, even if a product doesn’t exist in the Product table (all rows in our example).
• Right Outer Join: This would include all products, even those with no sales (would add rows for any products not yet sold).
• Full Outer Join: This would include all sales and all products (combines left and right outer joins).

Comparison: VLOOKUP vs Power Query

When to Use Each Method

Use VLOOKUP when:

• You need a quick, simple lookup.
• Your datasets are small (like our example with 7 orders).
• You’re working with a simple, one-time analysis.
• You don’t need to share the workbook with others.

Use Power Query when:

• Working with larger datasets (100+ rows).
• Performing complex mergers or transformations.
• Creating reports that need to be refreshed regularly.
• Building a repeatable process.
• Sharing your work with others.

Conclusion

Merging tables is a critical Excel skill. While VLOOKUP is good for basic tasks and available for decades. Power Query is the future of data merging, offering flexibility and efficiency. We explained all the steps with a practical dataset, you can choose any of the tools mainly based on your dataset size. For our small example dataset, either method works well. But as your data grows or your analysis becomes more complex, Power Query’s advantages become increasingly apparent. By mastering both techniques, you’ll be ready to merge tables like a pro regardless of the situation.