An inner join is one of the join types offered in Power Query’s Merge dialog box. An inner join only pulls matched rows of both tables. In this article, we are going to demonstrate to you two quick ways to inner join in Excel. If you are also curious about it, download our practice workbook and follow us.
Inner Join in Excel: 2 Quick Methods
To demonstrate the approaches, we consider two tables of examination marks. In both tables, we displayed the marks of math and physics with their ID.
All the operations of this article are accomplished by using Microsoft Office 365 application.
1. Applying VLOOKUP Function to Inner Join in Excel
In the first method, we are going to use the VLOOKUP function to inner join both tables in Excel. We will export the data according to our ID. The steps of this process are given below:
📌 Steps:
- First of all, in a new sheet, store all the IDs in the range of cells B5:B14.
- Now, select cell C5 and write down the following formula in the cell.
=VLOOKUP(B5,Dataset!$B$5:$C$14,2,FALSE)
- Hence, press Enter.
- Similarly, select cell D5 and write down the following formula inside the cell.
=VLOOKUP(B5,Dataset!$E$5:$F$14,2,FALSE)
- After that, again, press Enter.
- Further, select the range of cells C5:D5 and drag the AutoFill Handle icon to copy the formula up to cell D14.
- Finally, you will get your desired output.
Thus. we can say that our formula works perfectly, and we are able to apply inner join to our table in Excel.
2. Using Power Query Feature to Inner Join in Excel
In the second method, we will use the Power Query feature of Excel to inner join our data tables. The procedure of this method is explained below step-by-step:
📌 Steps:
- At first, select the range of cells B4:C14.
- Now, in the Data tab, click on the From Table/Range option from the Get & Transform group.
- As a result, a small dialog box titled Create Table will appear.
- Here, check the My table has header option and click OK.
- Hence, the Power Query Editor will appear.
- Then, in the Home tab, click on the drop-down arrow of the Close & Load command and choose the Close & Load To option.
- Another dialog box called Import Data will appear.
- After that, choose the Only Create Connection option and click OK.
- Similarly, follow the same procedure for the second table to create a connection.
- Next, in the Data tab, click on the drop-down arrow of the Get Data option and choose the Combine Queries > Merge option.
- As a result, the Merge dialog box will come in front of you.
- Now, from the upper field, click on the drop-down arrow and select Table1.
- Hence, click on the ID column.
- Similarly, follow the same procedure in the lower field.
- At last, in the Join Kind section as the Inner (only match rows) option.
- Finally, click OK.
- The Power Query Editor table will appear again.
- Afterward, click on the expand icon in the heading of Table2.
- Next, uncheck the ID option and click OK.
- At last, from the Home tab, click on the Close & Load option.
- You will see a new sheet will open, and it will show the joined table.
So, we can say that our procedure works successfully, and we are able to inner join in Excel.
Now, you may be curious to know how to outer join in Excel. The procedure of performing outer join is almost similar to the method mentioned above. You have to just choose a different option in the Join Kind section. Instead of the Inner (only match rows) option, you have to choose the Full Outer (all rows from both) option.
Read More: How to Combine Two Tables Using Power Query in Excel
How to Apply Left Join in Excel
Here, we will also show you the procedure of left join in Excel. The VLOOKUP function will help us to perform left join both tables in Excel. Our dataset is in the range of cells B5:G14.
The steps of this method are given as follows:
📌 Steps:
- First, in a new sheet, store the first table in the range of cells B5:C14.
- After that, select cell D5 and write down the following formula in the cell.
=VLOOKUP('Left Join'!B5,Left!$E$5:$G$14,{2,3},FALSE)
- Then, press Enter.
- Now, drag the AutoFill Handle icon to copy the formula up to cell D14.
- You will be able to left join in Excel.
Therefore, we can say that our formula works accurately, and we are able to perform the left join in Excel.
Download Practice Workbook
Download this practice workbook for practice while you are reading this article.
Conclusion
That’s the end of this article. I hope that this article will be helpful for you and you will be able to inner join in Excel. Please share any further queries or recommendations with us in the comments section below if you have any further questions or recommendations.
