The below dataset contains a table displaying, Student IDs and Names and another table containing Student IDs, Math, and Physics Scores. We will merge the lists and show them in a new single list.
Method 1 – Using VLOOKUP
Steps:
 To make a new list:
 Copy the left table and the heading of the Math and Physics Column of the right table.
 Paste it into a convenient place. We have placed it just below the old tables.
In cell D13, we will search for the Math scores of Abby.
 Enter 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.
 Lock the reference cells, as it is very important to copy the cell formula.
 Use the Fill Handle to AutoFill up to D17.
You can see that we have the Math scores of other corresponding students as well.
 To find the Physics score, go to cell E13 and enter the same formula:
=VLOOKUP($B13,$E$4:$G$8,3,FALSE)
 Use the Fill Handle to AutoFill up to E17
Method 2 – Applying Excel HLOOKUP
Steps:
 Copy the first list and the header column in the second list and paste them below, like this.
 Enter the following formula in cell D12:
=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.
 Use the Fill Handle to AutoFill up to D17.
 Enter the following formula in cell E13:
=HLOOKUP($B13,$E$5:$J$7,3,FALSE)
 Click Enter, and the result will look like this.
 Use the Fill Handle to AutoFill up to cell E17. You will get our desired final result.
Method 3 – Combining INDEX & MATCH Functions
3.1 Single Column
Steps:
 Enter 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 lookup range(Student Id Column in List 2)
 0 is for the exact match
 Press Enter, and you should get the following result
 Drag down the Fill Handle on cell D13 to D17 to get the remaining corresponding cell values.
 To get the cell values in the Physics column, enter 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 lookup range(Student Id Column in List 2)
 0 is for the exact match
You should get the following result:
 Drag down the Fill Handle on the cell E13 to E17 to get the remaining corresponding cell values.
3.2 Multiple Columns
Steps:
 Enter the following formula in cell E4:
=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.
Lock the cell reference mentioned above accordingly otherwise, we will not be able to copy the formula.
 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.
Method 4 – Using the Consolidate Feature
Steps:
 Copy the Student ID and Name columns. Create a new column named Total Marks.
 Select cell range D13:D17.
 Go to the Data. Select the Consolidate feature. (See image below)
 In the dialogue box, choose the function Sum.
 Select the reference for the marks of Math.
 Click Add.
 Add the marks for Physics.
 Click OK
You will have the following result.
Method 5 – Using the Excel Power Query
Steps:
 Select cell range B3:C8.
 Press CTRL+T.
 Create Table box will appear. Check the My table has headers.
 Click OK.
 Here, Excel will generate a table for you like below.
 Create a table for G3:I8 like the one below.
 Give the tables a name. We named the first one St_Name and the 2nd one Numbers.
 Select any cell in Table St_Name and go to the Data tab. Choose From Table/Range in the Get & Transform Data group.
 A Power Query editor will open. Click on the Close & Load dropdown arrow, and select the Close & Load To option like in the figure below.
 A new dialogue box named Import Data will open. Select Only Create Connection, and click OK
 Do the same for Table Numbers. You will see a query tab on the right side of the screen. Here, you will see 2 queries. (See the figure)
 Go to the Data tab and from the Get Data dropdown list, select Combine Queries > Merge like the figure below.
 A new window named Merge will open. From the dropdown lists, choose the following, as shown in the figure. Now, you have to select the common column. So select Student ID in both tables, and they will be highlighted in green. Now click.
 A new window named Queries will open. In the Numbers column, click on the icon(Expand radio button), as shown in the figure.
 Another window will open. Uncheck Student ID and check to use the original column name as a prefix, and click OK.
 You will see a Math and Physics column showing in the Queries Window.
 To import this table, click on the Close & Load dropdown arrow and choose Close and Load To…
 In the Import Data window, select Table and Existing worksheet.
 Choose a suitable position for your merged table. We have selected B12.
 Click OK.
 You will have your desired merged data in an Excel table.
Things to Remember
 Use the first two methods for a quick merging of data sets.
 Use the Power Query method if you have a large data set in a table format.
 Use the Consolidate method to get the sum or average of the data sets.
Download the Practice Workbook
Download this workbook to practice.
<< Go Back to Cells  Merge  Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
That was so clear and concise, I loved it. However, I could not get the INDEX & MATCH(2) to work. Otherwise, great.
Hello PENELOPE JORDAN,
We are glad these methods were helpful to you. Though it seems, you are facing issues with the INDEX & MATCH method for multiple columns. If you are talking about the #NA! error using the given formula, it can be solved with an easy step. Just fill up the Product column with the proper value first and enter the given formula afterward. Thus, you will obtain the desired result.
Try this way and let us know if it works.
Regards,
Yousuf Khan Shovon