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.
Steps:
- From the Data tab, click on Get Data.
- From the drop-down menu, click on From Other Sources.
- Click on Blank Query.
The Power Query Editor will appear as follows.
- Enter the following formula:
=Excel.CurrentWorkbook()- Click on the Double Arrow, as shown in the picture below.
- A dialogue box will appear. Check Select All Columns.
- Press OK.
- A combined dataset will appear in the Power Query Editor. Click on Close & Load.
- A combined data table will appear in the new worksheet.
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.
Steps:
- 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.
- Rename the E column as Salary. The merged table will appear as follows.
Method 3 – Using the INDEX MATCH Functions
Steps:
- 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
Method 4 –Â Matching Multiple Columns
Steps:
- 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.
Method 5 – Utilizing the XLOOKUP Function
Steps:
- Enter the following formula in cell E5:
=XLOOKUP(C5,$F$4:$F$17,$G$4:$G$17)- 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.
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
- Merge Two Tables in Excel and Remove Duplicates
- Merge Two Tables Based on One Column
- Merge Tables From Different Sheets
- Merge Two Tables in Excel Using Vlookup
- Merge Two Tables in Excel
<< Go Back to Merge in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
