How to Merge Tables in Excel ( 5 Methods)

Method 1 – Using the Power Query Tool

Below are two different tables for Physics A and Physics B. We will combine two tables from multiple worksheets with the Power Query Tool, combining the Physics marks from two sections of classes A and B.

Datasets of two Tables


  • From the Data tab, click on Get Data.
  • From the drop-down menu, click on From Other Sources.
  • Click on Blank Query.

Pressing on Blank Query from Drop Down Menu

The Power Query Editor will appear as follows.

  • Enter the following formula:
  • Click on the Double Arrow, as shown in the picture below.
  • A dialogue box will appear. Check Select All Columns.
  • Press OK.

Pasting the formula and Pressing OK

  • A combined dataset will appear in the Power Query Editor. Click on Close & Load.

Combined Data Appears in Power Query Editor

  • A combined data table will appear in the new worksheet.

A combined Table is Created

Method 2 – Using the VLOOKUP Function

We have two tables: Table 1: Employee Credentials and Table 2: Salary Data in two separate worksheets. We will use the VLOOKUP function to combine these two tables into one common column.

Two Tables in Two Worksheets


  • Enter the following formula in cell E5:
=VLOOKUP($B5,'Lookup Table'!$B$5:$C$17,2,FALSE)
  • Press Ctrl+Shift+Enter.

How the Formula Works:

  • $B2 is the value you are looking for.
  • ‘Lookup Table’!$B$5:$C$17 is the table to search.
  • 2 is the number of the column from which to extract the value.

Using VLOOKUP Function to Combine Two Tables

  • Rename the E column as Salary. The merged table will appear as follows.

Combined Tables

Method 3 – Using the INDEX MATCH Functions


  • Enter the following formula:
=INDEX($G$5:$G$17, MATCH($B5, $F$5:$F$17, 0))
  • Press Ctrl+Shift+Enter.
  • The Salary column will merge with Table-1: Employee Credentials.

How the Formula Works:

Syntax: INDEX (return_range, MATCH (lookup_value, lookup_range, 0))

To modify the formula, change these parameters.

  • Return_range­ ­: $G$5:$G$17
  • Lookup_value : $B5
  • Lookup_range : $F$5:$F$17

Using INDEX-MATCH Function to Combine Two Tables

Method 4 –  Matching Multiple Columns


  • Enter the following formula:
=INDEX($G$5:$I$17, MATCH(1, ($C5=$G$5:$G$17) * ($D5=$H$5:$H$17), 0), 3)
  • Press Ctrl+Shift+Enter.
  • Salaries from Table 2 will be added to the Salary column of Table 1.

Using INDEX-MATCH Function to Combine Two Tables by Multiple Columns

Method 5 – Utilizing the XLOOKUP Function


  • Enter the following formula in cell E5:
  • Press Enter.

How the Formula Works:

  • C5” is the value you want to search for.
  • $F$4:$F$17″ represents the range of cells where you want to search for the value.
  • $G$4:$G$17” represents the range of cells from which you want to retrieve the corresponding value.

Using XLOOKUP Function to Combine Two Tables

Things to Remember

  • Check data accuracy and organization.
  • Identify a common field/column.
  • Sort both tables based on the common field/column.
  • Back up your data.
  • Choose the appropriate merge method (e.g., VLOOKUP, INDEX-MATCH).

Download the Practice Workbook

Merge Tables in Excel: Knowledge Hub

<< Go Back to Merge in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Zahid Shuvo
Zahid Shuvo

Zahid Hasan Shuvo, a Naval Architecture and Marine Engineering graduate from BUET, Bangladesh, has contributed nearly a year to the Exceldemy Project as an Excel and VBA Content Developer. Within this timeframe, he has crafted over 8 tutorial articles, and besides offering valuable solutions to aid users effectively. Zahid also expresses keen interests in Excel & VBA, Data Analysis, Machine Learning, AI Engines, and Prompt Engineering, showcasing a diverse skill set and contributing to the dynamic environment of... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF