# How to Create Union of Two Tables in Excel (6 Simple Methods)

Get FREE Advanced Excel Exercises with Solutions!

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.

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 a 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. Utilizing 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 the 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 be displayed.

## 2. Creating Union of Tables with the 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.

## 3. Combining 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.

• Afterward, click the G4 cell with the resulting brush-shaped cursor.
• Consequently, we will achieve the desired outcome.

Read More: How to Do Union of Two Columns in Excel

## 4. Applying 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.

## 5. Using the 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. Joining 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.

You are welcome to download a free copy of the sample workbook we used in the lecture.

## Conclusion

You can create the union of two tables in Excel using our covered procedures.Â 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.

## Related Articles

<< Go Back to Excel Union

## What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.

Tags:

Lutfor Rahman Shimanto

Lutfor Rahman Shimanto, BSc, Information Technology, Jahangirnagar University, Bangladesh, has worked with the ExcelDemy project for over a year. He has written 50+ articles and provided solutions of 100+ comments for ExcelDemy. Currently, he works as an Excel & VBA Developer and provides support and solutions in the ExcelDemy Forum. He has solved 100+ ExcelDemy Forum problems. His work and learning interests are in developing various Excel & VBA and Desktop applications. Outside of work, he enjoys Chess... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF