Zehad Rian Jim
This tutorial will demonstrate how to perform left outer join in Excel. When we are dealing with many data tables that represent information about a certain person or company then we need to merge them. We can easily do the work by using the left outer join in Excel. With this feature, we can easily merge the table columns according to our needs. It not only saves our time but also eases our work. So, it is essential to learn how to perform left outer join in Excel.
Download Practice Workbook
You can download the practice workbook from here.
2 Effective Methods to Perform Left Outer Join in Excel
We’ll use a sample dataset overview as an example in Excel to understand easily. For instance, we have two tables. In the first table, we have Name and Social Science marks while in the second table, we have Name and Math marks. We will use this dataset to explain the whole process. You can create a table simply by selecting your data range and pressing Ctrl+T. If you follow the steps correctly, you should learn how to perform left outer join in Excel on your own. The methods are described below in order.
1. Merging Tables Using Power Query Editor
In this case, our goal is to perform a left outer join in Excel by using the Power Query feature. Power Query is a very powerful tool in Microsoft Excel 2010 and later. It can clean up and automate data in Excel. The Power Query is also known as “Get & Transform Data”. So the primary purpose of the Power Query can be divided into two main categories, Get Data & Transform Data. We can use this to fulfill our goal by following the below steps.
Steps:
- First, select the first table >> Table Design >> Table Name >> set the table name (in this case the name of the table is ScienceMarks).
- Next, repeat the same process to name the second table as MathMarks.
- After that, select the data table >> go to Data >> From Table/Range.
- Subsequently, the Power Query Editor will open on the screen and you will see your data there. Then, choose the Close & Load option to create the connection.
- Then, in the Import Data tab select the Only Create Connection option and press OK.
- Next, in the Queries and Connection tab, you will see the new connection accordingly.
- Furthermore, if you repeat the whole process for the second table, then you will get the connection for the second table as well.
- Moreover, go to Data >> Get Data >> Combine Queries >> Merge options.
- As a result, the Merge tab will open on the screen. In the Merge window, select the tables accordingly. Note that, after selecting the table you have to select the first columns of the tables as well and press OK.
- In addition, in the Power Query window, you will get the table where both tables have been merged accordingly.
- Hence, click on the arrow sign of the new column and select the data you want to show there( in this case select the Math option), and press OK.
- Next to that, you will get the data in the third column accordingly.
- Finally, press the Close and Load option to get the final result.
Hence, we have performed a left outer join in Excel by using the Power Query feature.
Read More: How to Join Tables in Excel (6 Suitable Methods)
2. Using VLOOKUP Function
Now, we want to perform the left outer join in Excel by using the VLOOKUP function. In Microsoft Excel, the VLOOKUP function is generally used to extract data based on a lookup value in a column or a range of cells. We can use this function to do a left outer join in Excel by following the below steps.
Steps:
- To begin with, create a data table similar to the image below. In the first table, we have Name and Social Science marks while in the second table, we have Name and Math We will use this dataset to explain the whole process.
- In addition, select the first table and press the Ctrl + C option to copy the table, and then press Ctrl + V option to paste the value in another blank cell.
- Furthermore, insert the following formula in the D11 cell.
=VLOOKUP(B11,$E$4:$F$9,2,FALSE)
Note that, in the formula, the first value (B11) presents the look-up value, the second portion $E$4:$F$9 portion represents the table array we will use and the last portion (2) presents the second column of the selected data range.
- Therefore, you will get the result for this cell. In this case, you will get the 2nd column of the E4:F9 range data which is Math. After that, use the Fill Handle option to execute the formula to all the cells of this column accordingly.
- Finally, you will get the data from the second column here. By this, you have merged the two data tables accordingly. In this new table, the first two columns are the values of the first table and in the third column, you have the values of the second table.
Therefore, we have performed the left outer join in Excel by using the VLOOKUP function.
Read More: How to Perform Left Join in Excel (3 Easy Ways)
Conclusion
Henceforth, follow the above-described methods. These methods will help you to perform left outer join in Excel. We will be glad to know if you can execute the task in any other way. Follow the ExcelDemy website for more articles like this. Please feel free to add comments, suggestions, or questions in the section below if you have any confusion or face any problems. We will try our best to solve the problem or work with your suggestions.
