EDUCBA

EDUCBA

MENUMENU
  • Explore
    • Lifetime Membership
    • All in One Bundles
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Login
Home Excel Excel Resources Excel Tools Excel Merge Two Tables

Excel Merge Two Tables

Jeevan A Y
Article byJeevan A Y
Madhuri Thakur
Reviewed byMadhuri Thakur

Merge Two Tables in Excel

Merge Two Tables in Excel (Table of Contents)

  • Merge Two Tables in Excel
  • Examples of Merge Two Tables in Excel

Merge Two Tables in Excel

Merging tables in Excel is not possible when both the table at least one common column. If the first condition is satisfied, then we can merge the tables with VlookUP and Index Match function. With the Vlookup function’s help, we can get the values from the second table to the first table if the lookup value matches with the Lookup range. And if we want to use the Index function along with Match to merge the tables, we must also have at least one common value to map.

ADVERTISEMENT
Popular Course in this category
MS Excel Course Bundle - 36 Courses in 1 | 13 Mock Tests

Start Your Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

Examples of Merge Two Tables in Excel

Below are the different examples to merge to tables in Excel:

You can download this Merge Two Tables Excel Template here – Merge Two Tables Excel Template

Example #1: Merge with Copy Paste Method

This is the easiest method to do. It does not involve any kind of formula or rocket science. We just simply do the copy & paste method to combine the two. I have two months of sales tables. One is Jan, and another one is Feb month sales data.

Merge Two Tables in Excel example 1

I have two identical tables here but not together. We can do two simple merges here. One is creating a new table altogether or add data to the existing table. If you observe, the headings of both the tables are the same.

Alternative 1

Since I have the region as the same heading, I can merge a table like the below one.

Merge Two Tables in Excel example 1.2

In the above image, I have excluded the Region heading because it is common for both tables.

Alternative 2

To make the data more flexible for analysis, I can just modify the data structure as shown in the below table.

Merge Two Tables in Excel example 1.3

Now look at the above table, I have merged the one below the other, but I have added one more column as Month. This is to analyze the data by applying the pivot table; the above data structure is important. We can easily apply the pivot table for the above table.

Example #2: Merge with VLOOKUP Method

Who does not know VLOOKUP? All most all excel users are aware of it; I hope you are also aware of it. If you the full usage of VLOOKUP, merging tables will be easier for you. I have three tables of employee’s information. The first table shows the Emp ID and their name, the second table shows the Emp ID and their department, the third table shows the Emp ID and their salary information.

Merge Two Tables in Excel example 1.4

If you look at all three tables, EMP ID is the common heading in all three tables. Based on this common heading, we can merge all three tables together. Merging should be in the below format.

Merge Two Tables in Excel example 1.5

I need to merge Department and Salary columns from Table 2 and Table 3. If you are aware of the VLOOKUP formula, then this will be a walk in the park.

Merge Two Tables in Excel vlookup formula

  • Apply VLOOKUP formula for Department from table 2 :

Merge Two Tables in Excel vlookup formula.1

  • Apply VLOOKUP formula on cell N2 of department column :

Merge Two Tables in Excel vlookup formula.2

  • Apply the formula as shown below :

Merge Two Tables in Excel vlookup formula.3

  • The result is shown in the below image :

Merge Two Tables in Excel vlookup formula.4

  • Now drag down the formula to all the empty cells :

Drag Down 1

  • Apply VLOOKUP formula for Salary from table 3:

VLOOKUP formula.6

  • Apply VLOOKUP formula on cell O2 of Salary column :

Merge Two Tables in Excel vlookup formula.7

  • Apply the formula as shown below :

Merge Two Tables in Excel vlookup formula.8

  • The result is shown in the below image :

Merge Two Tables in Excel vlookup formula.9

  • Now drag down the formula to all the empty cells :

Drag and Drop

Now we have merged all the data from the different table together by applying the VLOOKUP method.

Example #3: Merge with INDEX & MATCH Method

INDEX & MATCH would be a new formula for you. But nothing to worry it is just an alternative formula for the VLOOKUP formula. It works exactly the same as the VLOOKUP formula.

Merge Two Tables in Excel index formula.

  • Use the below formula for merging the Department column from Table 2:

Merge Two Tables in Excel index formula.1

  • Apply VLOOKUP formula on cell L3 of department column :

V Lookup Formula

  • Apply the formula as shown below :

INDEX & MATCH formula.3

  • The result is shown in the below image :

Merge Two Tables in Excel index formula.4

  • Now drag down the formula to all the empty cells :

Drag down

  • Use the below formula for merging the Salary column from Table 3 :

INDEX & MATCH formula.6

  • Apply VLOOKUP formula on cell N2 of department column :

Merge Two Tables in Excel index formula.7

  • Apply the formula as shown below :

Merge Two Tables in Excel index formula.8

  • The result is shown in the below image :

Merge Two Tables in Excel index formula.9

  • Now drag down the formula to all the empty cells :

Drag Down 2

Things to Remember about Merge Two Tables in Excel

  • POWER QUERY can merge many tables together. In Excel 2010 and in 2013, it is an add-in, and in excel 2016, it is a built-in function.
  • If you are merging by using VLOOKUP, you need to be careful of duplicate entries in the lookup value. Check for duplicate values before you proceed.
  • Once the formula is applied, remove the formula by using the paste special method because it will hamper your merged data table if something happens to other dependent tables.
  • You can use the IFERROR function to get rid of errors generated by the VLOOKUP formula in case of errors.
  • If the data is in many worksheets with the same headings, you can search for VBA code to combine worksheets together.

Recommended Articles

This has been a guide to Merge Two Tables in Excel. Here we discuss the Merge Two Tables in Excel and how to Merge Two Tables in Excel along with practical examples and downloadable excel template. You can also go through our other suggested articles –

  1. Sort Column in Excel
  2. AutoFilter in Excel
  3. Lookup Table in Excel
  4. Dynamic Tables in Excel
ADVERTISEMENT
MICROSOFT POWER BI Course Bundle - 8 Courses in 1
34+ Hours of HD Videos
8 Courses
Verifiable Certificate of Completion
Lifetime Access
4.5
ADVERTISEMENT
MS Excel & VBA for Data Science Course Bundle - 24 Courses in 1 | 10 Mock Tests
87+ Hours of HD Videos
24 Courses
10 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
ADVERTISEMENT
All-in-One Financial Analyst Masters Training Program Bundle - 550+ Courses | 300+ Mock Tests | 2000+ Hours | Lifetime |
2000+ Hours of HD Videos
43 Learning Paths
550+ Courses
Verifiable Certificate of Completion
Lifetime Access
4.9
ADVERTISEMENT
All-in-One Data Science Bundle - 400+ Courses | 550+ Mock Tests | 2000+ Hours | Lifetime |
2000+ Hour of HD Videos
80 Learning Paths
400+ Courses
Verifiable Certificate of Completion
Lifetime Access
4.7
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

Download Merge Two Tables Excel Template

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

Download Merge Two Tables Excel Template

EDUCBA

डाउनलोड Merge Two Tables Excel Template

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

🚀 Extended Cyber Monday Price Drop! All in One Universal Bundle (3700+ Courses) @ 🎁 90% OFF - Ends in ENROLL NOW