How to Merge Datasets in Excel (5 Easy Methods)

In this era of data-driven world, merging data sets is an essential skill. Many times we have discrete data spreading throughout an excel sheet but we may need to merge those data into a single form.  Hence, to know how to merge datasets in excel in those circumstances, read this article carefully.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


5 Useful Methods to Merge Datasets in Excel

In this section, we will demonstrate 5 effective methods on how to merge data in excel with appropriate illustrations. But first let’s look at an example in which we have a list of data containing Student IDs and Names and another list containing Student IDs, Math, and Physics Scores.

Information about students

Now, we want to merge both lists and show them in a new single list. To know how we can accomplish this, read the methods mentioned below.


1. Use VLOOKUP to Merge Datasets in Excel.

The VLOOKUP function is very useful when it comes to merging different datasets. To apply the function, follow the steps below.

Steps:

  • To make a new list, first, copy the left table and heading of the Math and Physics Column of the right table and paste it into your convenient place. For ease of illustration, here we have placed it just below the old tables.

Use VLOOKUP to Merge Datasets in Excel

  • Now, on cell D13, we will be searching for Math scores of Abby. To do that, write the following formula.
=VLOOKUP($B13,$E$4:$G$8,2,FALSE)

Here,

  • $B13 is the value to be searched.
  • $E$4:$G$8 is the range where the value will be searched in the first column.
  • 2 is the column number from where data will be extracted (Math has column number 2).
  • FALSE is for exact matching.

Use VLOOKUP to Merge Datasets in Excel

  • Make sure that you have properly locked the reference cells as it is very important for copying the cell formula.
  • Now, use the Fill Handle to AutoFill up to D17.

  • Here you can see that we have got the Math score of other corresponding students as well.
  • Now similarly to find the Physics score, go to cell E13 and write the same formula just in case of 2, write 3 as Physics scores are on the 3rd column.
=VLOOKUP($B13,$E$4:$G$8,3,FALSE)

Use VLOOKUP to Merge Datasets in Excel

  • Now, similarly use the Fill Handle to AutoFill up to E17

Use VLOOKUP to Merge Datasets in Excel

Read More: How to Merge Multiple Cells in Excel at Once (3 Quick Ways)


2. Apply HLOOKUP to Merge Datasets in Excel

When the lists of data are not oriented in a similar way to the one below, we can use the HLOOKUP function.

Apply HLOOKUP to Merge Datasets in Excel

To merge both lists of data, follow the steps below.

Steps:

  • Similar to the previous method, copy the first list and the header column in the second list and paste them below like this.

Apply HLOOKUP to Merge Datasets in Excel

  • Now on the cell D12, write the following equation
=HLOOKUP($B13,$E$5:$J$7,2,FALSE)

Here,

    • $B13 is the value to be searched
    • $E$5:$J$7 is the range where the value will be searched in the first column
    • 2 is the row number from where data will be extracted (Math has the column number 2)
    • FALSE is for exact matching.
  • Click Enter and you will get the following result.

Apply HLOOKUP to Merge Datasets in Excel

  • Now, use the Fill Handle to AutoFill up to D17


  • Now on the cell E13, write the formula below,
=HLOOKUP($B13,$E$5:$J$7,3,FALSE)

Click Enter, and the result will be like this.

Apply HLOOKUP to Merge Datasets in Excel

  • Now, use the Fill Handle to AutoFill up to cell E17. You will get our desired final result.

Read More: How to Merge Cells in Excel Table (7 Ways)


3. Use Combination of INDEX & MATCH Functions to Merge Datasets in Excel.

Applying the formula by combining MATCH and INDEX functions is another good alternative to VLOOKUP/HLOOKUP functions. Here we can even match columns based on multiple criteria. But let’s see the single column first.


3.1 Single Column

This time, we will use our previous example. We assume that we have already copy paste the 1st list and Math and Physics Header Column below the lists. Now follow the steps below:
Steps:

  • Write down the following formula in cell D13
=INDEX($F$4:$F$8,MATCH($B4,$E$4:$E$8,0),0)

Here,

    • $F$4:$F$8 is the return range(Math column in List 2)
    • $B4 is the lookup value(1612001)
    • $E$4:$E$8 is the look-up range(Student Id Column in List 2)
    • 0 is for the exact match
  • Press Enter, and you should get the following result

Use Combination of INDEX & MATCH Functions to Merge Datasets in Excel

  • Now drag down the Fill Handle on cell D13 to D17 to get the remaining corresponding cell values.

Use Combination of INDEX & MATCH Functions to Merge Datasets in Excel

  • Now to get the cell values on the Physics Column, write the following formula in E13.
=INDEX($G$4:$G$8,MATCH($B4,$E$4:$E$8,0),0)

Here,

  • $G$4:$G$8 is the return range(Physics column in List 2)
  • $B4 is the lookup value(1612001)
  • $E$4:$E$8 is the look-up range(Student Id Column in List 2)
  • 0 is for the exact match
  • You should get the following result

Use Combination of INDEX & MATCH Functions to Merge Datasets in Excel

  • Now drag down the Fill Handle on the cell E13 to E17 to get the remaining corresponding cell values.

Use Combination of INDEX & MATCH Functions to Merge Datasets in Excel

Read More: VBA to Merge Cells in Excel (9 Methods)


Similar Readings


3.2 Multiple Columns

This technique is helpful when you don’t have any unique identifier in the available data sets. Hence for illustration purposes, we have introduced a scenario where we don’t have any unique column.

So to merge the data of lists 1 and 2, we need to merge based on 2 columns( the Seller and Product columns ). Now follow the steps below to merge the given data.

Steps:

  • In cell E4, write down the following formula.
=INDEX($H$4:$J$13,MATCH(1,($C4=$H$4:$H$13)*($D4=$I$4:$I$13),0),3)

Here,

  • $H$4:$J$13 is the lookup table (2nd data table)
  • $C4=$H$4:$H$13 is equating to the value of cell C4 ( “Abby”) in the H4:H13 column( Seller column in the 2nd list).
  • $D4=$I$4:$I$13 is equating the value of cell D4 ( “Mouse”) in the I4:I13 column( Product column in the 2nd list)
  • 0 is for exact matching
  • 3 is for returning the value from the 3rd column of the 2nd list.
  • Remember to lock the cell reference mentioned above accordingly otherwise, we will not be able to copy the formula.

Use Combination of INDEX & MATCH Functions to Merge Datasets in Excel.

  • Now drag down the Fill Handle on cell E4 to E13 to get the remaining corresponding cell values.

  • After formatting the cells we will get our desired final result.

Use Combination of INDEX & MATCH Functions to Merge Datasets in Excel.

Read More: Merge Data in Excel from Multiple Worksheets (3 Methods)


4. Utilize Consolidate Feature to Merge Datasets in Excel

The Consolidate Feature is useful for summarising data from different lists. For Example, if we want to merge the above two lists by summing up the Math and Physics score for each student, we can use the Consolidate feature. But the major drawback of this feature is that the data have to be organized. So if you don’t have organized data, you have to make it organized like this one.

Utilize Consolidate Feature to Merge Datasets in Excel

Here, we have reorganized data based on the Student ID column in both lists. Now to know how to apply the Consolidate feature, follow the steps below.

Steps:

  • Below the lists, Copy the Student ID and Name Column like this. Also, create a new column named Total Marks.

Utilize Consolidate Feature to Merge Datasets in Excel

  • Now, select D13:D17.
  • Then, go to the Data After that, select Consolidate feature (see image)

Utilize Consolidate Feature to Merge Datasets in Excel

  • Now in the dialogue box, choose the function Sum.
  • Select the reference for the marks of Math.
  • Then, click Add.

  • Similarly, add the marks for Physics.
  • Then, click OK

Utilize Consolidate Feature to Merge Datasets in Excel

  • Now, you will have the following result.

Utilize Consolidate Feature to Merge Datasets in Excel

Read More: How to Merge Data from Multiple Workbooks in Excel (5 Methods)


5. Apply Power Query to Merge Datasets in Excel.

Power Query is another great tool for merging data. But to apply this Power Query we must convert our list into an Excel Table. But for ease of illustration, we have reorganized the table like below.

Apply Power Query to Merge Datasets in Excel

  • Now follow the steps below.

Steps:

  • Select B3:C8.
  • Then, press CTRL+T.
  • Create Table box will appear. Check the My table has headers
  • Then, click OK.

Apply Power Query to Merge Datasets in Excel

  • Here, Excel will generate a table for you like below.

Apply Power Query to Merge Datasets in Excel

  • Similarly, create a table for G3:I8 like this below.

Apply Power Query to Merge Datasets in Excel

  • Now, give the tables a name. We named the first one St_Name and the 2nd one Numbers.
  • Here is the naming of 1st table.

Apply Power Query to Merge Datasets in Excel

  • Here is the naming of the 2nd table.

  • Now, select any cell in Table St_Name and go to the Data tab. In this tab, chose From Table/Range in the Get & Transform Data group.

Apply Power Query to Merge Datasets in Excel

  • Here, a Power Query editor will open. Now, click on the Close & Load dropdown arrow, and select the Close & Load To option like in the figure below.

  • Here, a new dialogue box named Import Data will open. Now select Only Create Connection, and click OK

Apply Power Query to Merge Datasets in Excel

  • After that, do the same for Table Numbers. After that, you will see a query tab on the right side of the screen. Here you will see 2 queries. (See the figure)

Apply Power Query to Merge Datasets in Excel

  • Now, we will go to the Data tab and from Get Data drop-down list, select Combine Queries > Merge like the figure below.

Apply Power Query to Merge Datasets in Excel

  • Now,  a new window named Merge will open. Here from the dropdown lists choose the following shown in the figure. Now you have to select the common column. So select Student Id in both tables and they will be highlighted as green. Now click.

Apply Power Query to Merge Datasets in Excel

  • Here, a new window named Queries will be shown. Here in the Numbers column, click on the icon(Expand radio button) shown in the figure.

Apply Power Query to Merge Datasets in Excel

  • After clicking this icon, another window will open. Here uncheck Student ID and check to use the original column name as prefix and click OK.

  • Now,  you will see a Math and physics column showing in the Queries Window.

  • And now, to import this table, click on the Close & Load drop-down arrow and chose Close and Load To… In the Import Data window, select Table and Existing worksheet. Now choose a suitable position for your merged table. We have selected B12. Now, click OK.

Apply Power Query to Merge Datasets in Excel

  • Finally, you will have your desired merged data in the form of an Excel table.

Apply Power Query to Merge Datasets in Excel

Read More: How to Merge Cells in Excel Vertically Without Losing Data


Things to Remember

  • Use the first two methods for a quick merging of data sets.
  • If you have a large data set in table format, use the Power Query method.
  • Use Consolidate method if you need to get the sum or average of the data sets.

Conclusion

If you find this article helpful, please share this with your friends. Moreover, do let us know if you have any further queries. Finally, please visit Exeldemy for more exciting articles on Excel.


Related Articles

Aniruddah Alam

Aniruddah Alam

Hi, I am Md. Aniruddah Alam from Dhaka, Bangladesh. I have done my Bachelor's degree in Naval Architecture from BUET. I love to read books, listen to podcasts, explore new things, walking randomly in unknown places.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo