How to Merge Datasets in Excel (5 Easy Methods)

In this era of a 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.

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.

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. Using 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.

• Now, on cell D13, we will be searching for the Math scores of Abby. To do that, write the following formula.

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.

• 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 scores 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.

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

2. Applying Excel HLOOKUP to Merge Datasets

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

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.

• Now on the cell D12, write the following equation

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.

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

• Now on the cell E13, write the formula below,

Click Enter, and the result will be like this.

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

3. Combining INDEX & MATCH Functions to Merge Datasets

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 copied and pasted 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

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

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

• Now to get the cell values on the Physics Column, write the following formula in E13.

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

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

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.

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.

• 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.

4. Utilizing 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 scores for each student, we can use the Consolidate feature. However, 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.

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.

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

• 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

• Now, you will have the following result.

5. Applying Excel Power Query to Merge Datasets

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.

• 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.

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

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

• 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.

• 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, choose From Table/Range in the Get & Transform Data group.

• 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

• 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)

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

• 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.

• 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.

• 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 choose 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.

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

Things to Remember

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

Download Practice Workbook

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

Conclusion

In this article, we provided 5 methods of how to merge datasets in Excel. If you find this article helpful, please share this with your friends. Moreover, do let us know if you have any further queries.

<< Go Back to Cells | Merge | Learn Excel