Merge Tables in Excel

Merging Tables in Excel: A Comprehensive Guide

When working with data in Excel, it’s common to have information split across multiple tables. Whether you’re combining data from different sources or consolidating sheets, knowing how to merge tables efficiently is a valuable skill. In this guide, we’ll explore the various methods to merge tables in Excel, including using formulas, Power Query, and VBA scripts.

Before diving into the details, let's consider a scenario where you have two tables:

Table 1	Table 2
ID, Name, Age	ID, Department, Salary
1, John, 25	1, Sales, 50000
2, Alice, 30	2, Marketing, 60000

The goal is to merge these tables based on the ID column. The resulting table should contain all the columns from both tables.

Method 1: Using VLOOKUP Formula

One of the simplest ways to merge tables is by using the VLOOKUP formula. This method is useful when you have a unique identifier in one table that you can use to look up corresponding values in another table.

Here’s how to do it:

• Assume Table 1 is in the range A1:C3 and Table 2 is in the range E1:G3.
• In a new range (say, I1:K3), enter the headers from both tables.
• In cell I2, enter the formula: =VLOOKUP(A2, E:G, 2, FALSE) to fetch the Department.
• In cell J2, enter the formula: =VLOOKUP(A2, E:G, 3, FALSE) to fetch the Salary.
• Drag these formulas down to fill the rest of the cells.

Method 2: Using INDEX/MATCH Function

The INDEX/MATCH function combination is more flexible and powerful than VLOOKUP, especially when dealing with large datasets or when the column you’re looking up is to the left of the column you want to return.

Here’s how to use it:

• Assuming the same table ranges as before.
• In cell I2, enter the formula: =INDEX(G:G, MATCH(A2, E:E, 0)) to fetch the Salary.
• In cell J2, you might need to adjust the column index if you’re fetching from a different column.
• Drag these formulas down.

Method 3: Using Power Query

Power Query (available in Excel 2010 and later versions) offers a more visual and interactive way to merge tables. It’s especially useful when working with large datasets or when you need to perform complex data transformations.

To merge tables using Power Query:

• Go to the “Data” tab and click on “From Table/Range” to load your first table into Power Query.
• Click on “Merge Queries” and select your second table.
• Choose the columns to match on (in this case, ID) and select the type of join you want (e.g., inner join).
• Click “OK” and then “Load” to load the merged data into a new sheet.

Method 4: Using VBA Script

For those comfortable with coding, a VBA script can automate the merging process, especially useful for repetitive tasks or when working with very large datasets.

A basic VBA script to merge two tables might look like this:

Sub MergeTables()

Dim ws As Worksheet

Set ws = ThisWorkbook.Sheets(“Sheet1”)

Dim lastRow1 As Long, lastRow2 As Long

lastRow1 = ws.Cells(ws.Rows.Count, “A”).End(xlUp).Row

lastRow2 = ws.Cells(ws.Rows.Count, “E”).End(xlUp).Row

‘ Loop through and merge based on ID

For i = 2 To lastRow1

For j = 2 To lastRow2

If ws.Cells(i, 1).Value = ws.Cells(j, 5).Value Then

’ Copy data from Table 2 to the merged area

ws.Cells(i, 4).Value = ws.Cells(j, 6).Value

ws.Cells(i, 5).Value = ws.Cells(j, 7).Value

End If

Next j

Next i

End Sub

💡 Note: This script assumes your data is structured in a specific way and may need adjustments based on your actual data layout.

Choosing the Right Method

The method you choose depends on the size and complexity of your data, your comfort level with formulas or coding, and whether you need to perform this task regularly. For simple, one-time merges, VLOOKUP or INDEX/MATCH might suffice. For more complex or recurring tasks, Power Query or VBA could be more efficient.

In the end, mastering how to merge tables in Excel opens up a wide range of data manipulation possibilities, enabling you to analyze, report, and make decisions based on your data more effectively. By understanding and applying these methods, you can significantly enhance your productivity and the quality of your work in Excel.