Unquestionably, Microsoft Excel excels at crunching numbers! Now, this means that you can merge datasets and perform tedious calculations in the blink of an eye. In this regard, Excel has become a convenient and valuable tool. Keeping this in mind, this article demonstrates how to perform outer join in Excel. In addition, we’ll also learn to execute full outer and inner-outer joins in Excel.
Perform Outer Join in Excel: 2 Ways
First of all, let’s consider the List of Customer Orders dataset shown in the B4:D14 cells containing the “Product ID”, “Customer”, and “Date” columns respectively.
Similarly, let’s suppose we have the List of Product and Sales dataset shown in the B4:D14 cells with the “Product ID”, “Item”, and “Sales” in USD respectively. Here, we want to perform an outer join in Excel based on the common “Product ID” column present in the two datasets. Henceforth, without further delay, let’s follow each procedure step-by-step and with the appropriate illustrations.
Here, we have used the Microsoft Excel 365 version; you may use any other version according to your convenience.
1. Using IFERROR and VLOOKUP Functions
In the first place, let’s start by applying the IFERROR and VLOOKUP functions to carry out an outer join in Excel. In this scenario, the VLOOKUP function pulls in the data from the “Order” and “Sales” worksheets and joins them together. Meanwhile, the IFERROR function handles any #N/A Error by returning blank.
📌 Steps:
- To begin with, go to the B4 cell >> press CTRL + T to create and insert a Table.
- Next, rename the Table to “Order_Tbl” as shown below.
- In a similar style, create a second Table >> name it “Product_Tbl”.
- Now, move to the C5 cell >> enter the formula given below.
=IFERROR(VLOOKUP([@[Product ID]],Order_Tbl,2,FALSE),"")
Here, Product ID refers to the column header while Order_Tbl is the Table range.
Formula Breakdown:
- VLOOKUP([@[Product ID]],Order_Tbl,2,FALSE) → looks for a value in the left-most column of a table, and then returns a value in the same row from a column you specify. Here, [@[Product ID]] ( lookup_value argument) is mapped from the Order_Tbl (table_array argument) array in the “Order” worksheet. Next, 2 (col_index_num argument) represents the column number of the lookup value. Lastly, FALSE (range_lookup argument) refers to the Exact match of the lookup value.
- Output → “Sam”
- IFERROR(VLOOKUP([@[Product ID]],Order_Tbl,2,FALSE),””) → becomes
- IFERROR(“Sam”,“”) → returns value_if_error if the expression has an error and the value of the expression itself otherwise. Here, the “Sam” is the value argument, and “”(Blank) is the value_if_error argument. In this case, the function returns the name corresponding to the “Sam”.
- Output → “Sam”
- Afterward, navigate to the D5 cell >> type in the expression given below.
=IFERROR(VLOOKUP([@[Product ID]],Order_Tbl,3,FALSE),"")
- In turn, insert the equation below into the E5 cell.
=IFERROR(VLOOKUP([@[Product ID]],Product_Tbl,2,FALSE),"")
For instance, the Product_Tbl represents the Table range.
- Lastly, enter the F5 cell >> insert the expression into the Formula Bar to get the results shown in the picture below.
=IFERROR(VLOOKUP([@[Product ID]],Product_Tbl,3,FALSE),"")
2. Utilizing Power Query Editor
Alternatively, we can also apply another nifty feature of Excel, simply put, the PowerQuery Editor which can transform and merge large datasets with relative ease. Fortunately, PowerQuery has a built-in merge option to combine datasets, so just follow along.
📌 Steps:
- First, select the B4 cell in the “Order” worksheet >> navigate to Data tab >> select From Table/Range.
- Later, enable the Only Create Connection option >> hit OK.
Again, repeat the same procedure for the Table in the “Order” worksheet.
- Second, jump to the Get Data drop-down >> navigate to Combine Queries >> choose Merge option.
- Not long after, follow the steps shown in the animated GIF below.
- Third, press the Close & Load To option at the top-left corner.
- Next, in the Import Data window, check the Table and New Worksheet radio buttons >> press the OK button.
Ultimately, the final output should resemble the picture below.
How to Execute a Full Outer Join in Excel
Besides, we can execute a full outer join that combines all the rows from the two datasets/tables into one.
📌 Steps:
- At the very beginning, proceed to the “Best Sellers” worksheet >> create a Table and name it “Best_Sellers_Tbl”.
- Likewise, perform the same steps in the “Quantity” worksheet to create the “Quantity_Tbl”.
- Following this, Load and Establish a Connection for both Tables by following the prior steps.
- At this point, go to Get Data >> choose the Merge option from Combine Queries.
- Now, observe and carry out the steps in real-time according to the GIF below.
- Later, load the data into a New Worksheet as a Table.
Eventually, the output should appear in the screenshot below.
Read More: How to Perform Left Join in Excel
How to Accomplish an Inner Outer Join in Excel
Last but not least, performing an inner-outer join combines only the matching rows from the two datasets. Now, allow us to demonstrate the process in the steps shown below.
📌 Steps:
- In the first place, load the Tables into the PowerQuery editor >> Merge the two queries as shown below.
- Subsequently, Close & Load the transformed data into a new worksheet.
Read More: How to Inner Join in Excel
Practice Section
We have provided a Practice section on the right side of each sheet so you can practice yourself. Please make sure to do it by yourself.
Download Practice Workbook
Conclusion
To sum up, we hope this tutorial has provided you with helpful knowledge on how to perform outer join in Excel. Now, we recommend you apply all this know-how in the practice dataset by downloading the practice workbook. In addition, feel free to comment and provide your valuable feedback.
Related Articles
- How to Combine Two Tables Using Power Query in Excel
- How to Perform Left Outer Join in Excel
- How to Create Cross Join in Excel
<< Go Back to Power Query Joins | Power Query Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
