How to merge two tables in excel

Answer

How to Merge Two Tables in Excel

Merging two tables in Excel is the process of combining data from separate tables into one unified table. The method you use depends on whether the tables share a common column (like IDs or names) or if you want to append them one below the other.

1. Using VLOOKUP to Merge Tables with a Common Column

A common approach is to use VLOOKUP when both tables share a key column.

Steps:

1. Ensure both tables have a common column. For example, Table 1 has Employee ID and Name, and Table 2 has Employee ID and Department.

2. Select the cell where you want to display merged data (e.g., in Table 1, next to Name).

3. Enter the formula: 

   =VLOOKUP(A2, Table2!$A$2:$B$100, 2, FALSE)

• A2 is the key in Table 1 (Employee ID).

• Table2!$A$2:$B$100 is the range of Table 2.

• 2 indicates the column number in Table 2 to retrieve the (Department).

• FALSE ensures an exact match.

4. Drag the formula down for all rows. Table 1 now includes the department information from Table 2.

2. Using XLOOKUP (Recommended in Latest Excel Versions)

XLOOKUP is more flexible and avoids column index issues.

Steps:

1. Select the cell in Table 1 where the merged data should appear.

2. Enter the formula: 

   =XLOOKUP(A2, Table2!A:A, Table2!B:B, "Not Found")

• A2 is the lookup value from Table 1.

• Table2!A:A is the lookup array (key column in Table 2).

• Table2!B:B is the return array (column to retrieve).

• "Not Found" is displayed if no match exists.

3. Press Enter and drag down for all rows.

3. Using Power Query to Merge Tables

Power Query is ideal for larger datasets and advanced merging.

Steps:

1. Go to Data → Get & Transform Data → Get Data → From Table/Range for Table 1.

2. Repeat the process for Table 2.

3. In the Power Query editor, click Home → Merge Queries.

4. Select the matching column in both tables.

5. Choose the Join Kind:

   • Left Join: Keeps all rows from the first table and matches from the second.

   • Inner Join: Keeps only rows present in both tables.

   • Right Join: Keeps all rows from the second table and matches from the first.

   • Full Outer Join: Keeps all rows from both tables.

6. Click OK, expand the merged columns, and load the data back to Excel.

4. Appending Tables (One Below the Other)

If both tables have identical columns and you want to stack them:

1. Copy the second table.

2. Paste it below the first table in the same columns.

3. Excel will automatically extend the table if both are formatted as tables. Otherwise, select both ranges and use Insert → Table to combine them.

5. Tips for Merging Tables

1. Ensure column names match when using VLOOKUP or XLOOKUP.

2. Avoid duplicate rows by checking key columns.

3. Use Remove Duplicates under Data → Data Tools after merging.

4. Format both tables as tables (Ctrl + T) for dynamic ranges.

This approach works on both Windows and Mac versions of Excel. On Mac, the formula structure is identical, but menu labels may slightly differ (e.g., Data → Get Data → From Table/Range might appear as Data → Get & Transform → From Table).

Yes · 0 No · 0 Add Comment · 0

Add ·

Report