How to merge two excel spreadsheets based on one column

Answer

Merging two Excel spreadsheets based on one column is done by matching values in a common column, often called a key column. This operation is similar to a database join. Excel provides multiple ways to achieve this, including VLOOKUP, XLOOKUP, INDEX-MATCH, and Power Query.

Method 1: Using VLOOKUP

VLOOKUP searches for a value in one table and returns a corresponding value from another table.

Steps:

1. Open both spreadsheets or copy both tables into the same workbook on separate sheets.

2. Identify the common column (key column) present in both sheets.

3. In the sheet where you want to merge data, choose a blank column next to your table.

4. Enter the formula:

   =VLOOKUP(lookup_value, table_array, col_index_num, FALSE)

   Explanation:

   • lookup_value → the cell in the key column of your first sheet.

   • table_array → the range of your second sheet including the key column and the column(s) to bring over.

   • col_index_num → the column number in the second sheet’s table to return.

   • FALSE → ensures an exact match.

5. Drag the formula down to fill the column.

Example:
You have Sheet1 with Employee ID and Name, and Sheet2 with Employee ID and Salary. To bring Salary into Sheet1: 

=VLOOKUP(A2, Sheet2!A:B, 2, FALSE)

Method 2: Using XLOOKUP (Excel 365 or 2021)

XLOOKUP is a modern replacement for VLOOKUP, supporting both left and right lookups.

Steps:

1. Select a blank column in the first sheet.

2. Enter the formula: 

   =XLOOKUP(lookup_value, lookup_array, return_array, "Not Found")

   Explanation:

   • lookup_value → cell in key column of your first sheet.

   • lookup_array → column with matching keys in the second sheet.

   • return_array → column with the data you want to retrieve.

   • "Not Found" → optional message if no match exists.

Example: 

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

This retrieves the corresponding Salary for each Employee ID.

Method 3: Using INDEX-MATCH

INDEX-MATCH provides more flexibility than VLOOKUP, especially for left-side lookups.

Steps:

1. Select a blank column in the first sheet.

2. Enter the formula: 

   =INDEX(return_range, MATCH(lookup_value, lookup_range, 0))

   Explanation:

   • return_range → column in second sheet to retrieve data from.

   • lookup_value → key column in first sheet.

   • lookup_range → key column in second sheet.

   • 0 → ensures exact match.

Example: 

=INDEX(Sheet2!B:B, MATCH(A2, Sheet2!A:A, 0))

This finds the Salary in Sheet2 for each Employee ID in Sheet1.

Method 4: Using Power Query

Power Query is the most robust method for merging large datasets.

Steps:

1. Go to the Data tab → Get Data → From File → From Workbook. Load both sheets.

2. In Power Query Editor, select one of the tables.

3. Click Merge Queries → choose the second table.

4. Select the key column in both tables.

5. Choose Join Kind (default is Left Outer for merging additional columns).

6. Click OK, then Close & Load to import the merged table into Excel.

Power Query automatically handles duplicate columns and missing values efficiently.

Tips and Notes

1. Ensure the key columns are exactly matching. Remove extra spaces using TRIM() if necessary.

2. Format the key column consistently (numbers as numbers, text as text).

3. Large datasets benefit from Power Query over VLOOKUP or XLOOKUP for performance.

4. Excel formulas can break if rows are added in the source table; structured tables help maintain dynamic references.

Merging spreadsheets based on one column ensures accurate data integration without manual copying, reducing errors and saving time.

Yes · 0 No · 0 Add Comment · 0

Add ·

Report