Lutfor Rahman Shimanto
There’s no denying that Microsoft Excel is a powerful tool. We can do many operations on a dataset using Excel’s built-in tools and functions. This article presents six straightforward methods for merging tables. Therefore, you should use these 6 easy ways to create the Union of Two Tables in Excel.
Download Practice Workbook
You are welcome to download a free copy of the sample workbook we used in the lecture.
6 Simple Methods to Create Union of Two Tables in Excel
To provide a demonstration, let’s investigate a sample dataset. For instance, the following dataset has two tables. The left table contains two columns titled Product Name and Cost Price. Another table is on the right side, holding the Product Name column like the previous one and a unique column named Profit. We will use each of the 6 methods to create union of two tables in Excel. In addition, I have yet to say that I have been working with the Microsoft Excel 365 version for this post. Nevertheless, you are free to use any other edition that will provide you with the most significant amount of convenience.
1. Utilize Excel VLOOKUP Function to Join Two Tables
In Excel, a built-in function called VLOOKUP enables searching across several columns. Follow the steps listed below to complete the task effectively with the help of the VLOOKUP function.
STEPS:
- First, select the G4Â cell.
- Second, input the following formula in G4.
=VLOOKUP(E4,$B$4:$C$10,2,FALSE)
- After that, hit Enter or Tab key.
- Consequently, we will see the outcome below.
- Presently, we need to apply the same formula for the subsequent cells.
- To achieve this, hold the Fill Handle icon and move it to G10.
- At this time, select the C4:C10Â range.
- Later, go to the Home tab.
- Afterwards, from the Clipboard group, click the Format Painter icon.
- Due to this, a brush cursor will appear, then click the G4Â cell.
- As a result, the desired output will display.
Read More: How to Do Union of Two Columns in Excel (5 Easy Ways)
2. Create Union of Tables with XLOOKUP Function
In Microsoft Excel, the XLOOKUP function is a sophisticated search tool that allows users to discover specific numbers from a group of cells. Follow the procedures outlined below to finish the operation using the XLOOKUP function.
STEPS:
- First, choose the G4Â cell.
- Second, type the below formula in G4.
=XLOOKUP(E4,$B$4:$B$10,$C$4:$C$10)
- After that, press Enter or Tab key.
- Due to this, we will see the outcome below.
- Eventually, we need to apply the same formula for the subsequent cells.
- At this point, hold the Fill Handle icon and move it to G10.
- Currently, select the C4:C10Â range.
- Then, navigate to the Home tab.
- Later, within the Clipboard group, select the Format Painter symbol.
- As a result, a brush cursor will appear, then click the G4Â cell.
- Thus, it will produce the intended result.
Read More: How to Make Union of Two Sheets in Excel (4 Suitable Methods)
3. Combine INDEX and MATCH Functions to Merge Tables
The INDEX & MATCH functions provide a vital tool for doing a simple and complicated search in Excel. In this part, we’ll combine these functions to combine two tables. To complete the work successfully, follow the procedures given below.
STEPS:
- Initially, select the G4Â cell.
- Secondly, type the following formula in cell G4:
=INDEX($C$4:$C$10,MATCH($E4,$B$4:$B$10,0))
- Press Enter or Tab to continue.
- As a result, the consequence will be displayed below.
- Next, we must apply the same formula to the succeeding cells.
- Hold the Fill Handle symbol at this time and also shift it to G10.
- Select the C4:C10 range for the time being.
- After that, navigate to the Home tab next.
- Then, from the Clipboard group, choose the Format Painter symbol.
- Afterwards, click the G4 cell with the resulting brush-shaped cursor.
- Consequently, we will achieve the desired outcome.
4. Apply Excel Power Query to Combine Two Tables
In this context, we will fetch a column from the left table and join it with another table on the right using the Power Query.
STEPS:
- First, navigate to the Data tab.
- Second, choose the Get Data icon from the Get & Transform Data group.
- Subsequently, select the From Other Sources option, followed by the Form Table/Range.
- Due to this, the Create Table window will open.
- Later, type the range for the left table in the input box.
- After that, check the My Table Has Headers option and hit OK.
- Consequently, the Power Query Editor window will open to display the left table as Table1.
- Afterwards, close the Power Query Editor window.
- Next, tap the Keep button.
- It is essential to note that the table will be stored in a new sheet titled Table1 in this case.
- Likewise, follow the same procedures to create the table on the right side.
- Thus, the Power Query Editor window will show the right table as Table2.
- Then, select the Combine group, followed by the little Down Arrow icon, and later click Merge Queries.
- As a result, the Merge window will display.
- To continue, select the Product Name column from both Table1 and Table2.
- Now, pick the Left Outer from the Join Kind section and hit OK.
- Therefore, two tables will join and display like below.
- At this time, click the Expand icon.
- Later, check the Cost Price column and uncheck the Prefix option.
- Then, click OK.
- Next, from the Power Query Editor window, choose Close & Load, followed by Close & Load To.
- As a consequence, the Import Data window will appear.
- Now, check the Table option and the Existing Worksheet field.
- Later, type the Sheet Name with an Exclamation mark followed by the range and hit OK.
- Finally, it will produce the desired output below.
Read More: How to Perform Union Query in Excel (with Detailed Steps)
5. Use Copy-Paste Feature to Merge Tables
The use of the Copy-Paste feature is an additional exciting and clever tactic. For a large dataset, it is wise to avoid this procedure. To finish the assignment, please follow these procedures.
STEPS:
- To begin, select the C4:C10Â range.
- Second, tap Ctrl+C.
- After that, mark another field, in this case, G4:G10.
- Latterly, press Ctrl+V.
- Finally, we will obtain the intended output below.
6. Join Two Tables Through Excel VBA
We can develop VBA code for the Union of Two Tables and execute it. Please follow the instructions below to complete the assignment efficiently.
STEPS:
- First, navigate to Developer.
- Second, choose Visual Basic.
- After that, click Insert, followed by Module.
- Now, type the code below into the Module Box.
Sub MergeTable()
Worksheets("Excel VBA").Range("C4:C10").Copy
Worksheets("Excel VBA").Range("G4:G10").PasteSpecial
End Sub- To conclude, press F5 or select the Run symbol.
- Thus, it will provide the desired output below.
Read More: VBA Union Method to Join Range in Excel (3 Examples)
Conclusion
You can create the Union of Two Tables in Excel using our covered procedures. There are more articles like this one on the ExcelDemy Website. Keep employing those, and let us know if you have further suggestions for completing the task. Remember to leave any questions, remarks, or recommendations in the space below.
