How to Merge Two Tables in Excel Based on One Column?

Merging two tables in Excel based on a common column involves utilizing features like Power Query or VLOOKUP (depending on the desired outcome and Excel version). Here's a breakdown of the common methods:

Method 1: Using Power Query (Get & Transform Data)

Power Query offers a robust and flexible way to merge tables. It allows for various join types (left, right, inner, outer) depending on how you want to combine the data.

1. Load Tables into Power Query:
   • Select a cell within your first table.
   • Go to the "Data" tab and click "From Table/Range." This will open the Power Query Editor.
   • Repeat this process for your second table.
2. Merge Queries:
   • In the Power Query Editor, select one of your queries (tables).
   • Go to the "Home" tab and click "Merge Queries."
   • In the "Merge" dialog box:
     • Choose the second table from the dropdown.
     • Select the common column in both tables that you want to base the merge on by clicking on the column header in each table.
     • Choose the desired "Join Kind." This determines how rows are matched:
       • Left Outer: All rows from the first table and matching rows from the second table.
       • Right Outer: All rows from the second table and matching rows from the first table.
       • Inner: Only rows that have matching values in the selected column in both tables.
       • Full Outer: All rows from both tables, even if there's no match.
     • Click "OK."
3. Expand the Merged Column:
   • A new column will appear with the name of the second table. Click the double-headed arrow in the column header to expand the columns you want to include from the second table. Uncheck "Use original column name as prefix" if you don't want the original table name prepended to the new column names.
   • Click "OK."
4. Load the Result:
   • Go to the "Home" tab and click "Close & Load" (or "Close & Load To..." to choose a specific location for the merged table).

Method 2: Using VLOOKUP (For simpler scenarios)

VLOOKUP is a simpler method suitable if you want to bring specific data from one table into another based on a common column.

1. Identify the Lookup Value: This is the value in the common column in your primary table.

2. Use VLOOKUP Formula:

   • In the primary table where you want to add data, insert a new column.

   • Enter the following formula in the first cell of the new column:

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

     • lookup_value: The cell in the common column of the primary table (e.g., A2).
     • table_array: The entire range of the second table, including the common column and the column you want to retrieve data from (e.g., Sheet2!A:C). Make sure this range is absolute (e.g., Sheet2!$A:$C) so that it doesn't change when you drag the formula down.
     • col_index_num: The column number within the table_array from which you want to retrieve data (e.g., if you want to retrieve data from the third column of the table_array, enter 3).
     • [range_lookup]: Set this to FALSE for an exact match or TRUE (or omit it) for an approximate match. Using FALSE is strongly recommended for most merging operations to ensure accuracy.

   • Example: =VLOOKUP(A2,Sheet2!$A:$C,3,FALSE)

3. Drag the Formula: Drag the formula down to apply it to all rows in the primary table.

4. Handle Errors: If VLOOKUP can't find a match, it will return #N/A. You can use the IFERROR function to handle these errors, replacing them with a default value (e.g., blank or "Not Found"). For example: =IFERROR(VLOOKUP(A2,Sheet2!$A:$C,3,FALSE),"Not Found")

Example:

Let's say you have two tables:

Table 1 (Customers)

CustomerID	CustomerName
1	John Doe
2	Jane Smith
3	Peter Jones

Table 2 (Orders)

OrderID	CustomerID	OrderDate
101	1	2024-07-26
102	2	2024-07-27
103	1	2024-07-28

You can merge these tables based on the CustomerID column to add the Customer Name to the Orders table or vice versa.