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

The left table contains two columns titled Product Name and Cost Price. The right side holds the Product Name column and a column named Profit.

## Method 1 – Utilizing Excel VLOOKUP Function to Join Two Tables

STEPS:

• Select the G4Â cell.
• Input the following formula in G4.

`=VLOOKUP(E4,\$B\$4:\$C\$10,2,FALSE)`

• Hit the Enter or TabÂ key.
• You’ll see the outcome below.

• You need to apply the same formula for the subsequent cells.
• Hold the Fill Handle icon and move it to G10.

• Select the C4:C10Â range.
• Go to the HomeÂ tab.
• From the Clipboard group, click the Format PainterÂ icon.

• A brush cursor will appear, then click the G4Â cell.
• The desired output will be displayed.

## Method 2 – Creating Union of Tables with the XLOOKUP Function

STEPS:

• Choose the G4Â cell.
• Type the below formula in G4.

`=XLOOKUP(E4,\$B\$4:\$B\$10,\$C\$4:\$C\$10)`

• Press Enter or TabÂ keys.
• You will see the outcome below.

• You need to apply the same formula for the subsequent cells.
• Hold the Fill Handle icon and move it to G10.

• Select the C4:C10Â range.
• Navigate to the HomeÂ tab.
• Within the Clipboard group, select the Format PainterÂ symbol.

• A brush cursor will appear, then click the G4Â cell.
• Produce the intended result.

## Method 3 – Combining INDEX and MATCH Functions to Merge Tables

STEPS:

• Select the G4Â cell.
• 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.
• The consequence will be displayed below.

• Apply the same formula to the succeeding cells.
• Hold the Fill Handle symbol and also shift it to G10.

• Select the C4:C10 range for the time being.
• Navigate to the Home tab next.
• From the Clipboard group, choose the Format PainterÂ symbol.

• Click the G4 cell with the resulting brush-shaped cursor.
• You will achieve the desired outcome.

## Method 4 – Applying Excel Power Query to Combine Two Tables

STEPS:

• Navigate to the DataÂ tab.
• Choose the Get Data icon from the Get & Transform DataÂ group.

• Select the From Other Sources option, followed by the Form Table/Range.

• The Create Table window will open.
• Type the range for the left table in the input box.
• Check the My Table Has Headers option and hit OK.

• The Power Query Editor window will open to display the left table as Table1.

• Close the Power Query EditorÂ window.
• Tap the KeepÂ button.

• Note that the table will be stored in a new sheet titled Table1 in this case.
• Follow the same procedures to create the table on the right side.
• The Power Query Editor window will show the right table as Table2.

• Select the Combine group, followed by the little Down Arrow icon, and later click Merge Queries.

• The Merge window will display.
• Select the Product Name column from both Table1 and Table2.
• Pick the Left Outer from the Join Kind section and hit OK.

• Two tables will join and display, as shown below.
• Click the ExpandÂ icon.

• Check the Cost Price column and uncheck the PrefixÂ option.
• Click OK.

• From the Power Query Editor window, choose Close & Load, followed by Close & Load To.

• The Import Data window will appear.
• Check the Table option and the Existing WorksheetÂ field.
• Type the Sheet Name with an Exclamation mark followed by the range and hit OK.

• It will produce the desired output below.

## Method 5 – Using the Copy-Paste Feature to Merge Tables

STEPS:

• Select the C4:C10Â range.
• Tap Ctrl+C.

• Mark another field, in this case, G4:G10.
• Press Ctrl+V.
• You will obtain the intended output below.

## Method 6 -Joining Two Tables Through Excel VBA

STEPS:

• Navigate to Developer.
• Choose Visual Basic.

• Click Insert, followed by Module.

• 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``````
• Press F5 or select the RunÂ symbol.

• It will provide the desired output below.

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

## Related Articles

<< Go Back to Excel Union

Get FREE Advanced Excel Exercises with Solutions!

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