How to Perform Outer Join in Excel (2 Easy Ways)

Get FREE Advanced Excel Exercises with Solutions!

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.

Order Dataset for outer join in excel

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.

Product dataset for outer join in excel

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.

Creating Table to Use IFERROR and VLOOKUP Functions

  • Next, rename the Table to “Order_Tbl” as shown below.

Naming Excel Table

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

Using IFERROR and VLOOKUP Functions

  • Afterward, navigate to the D5 cell >> type in the expression given below.

=IFERROR(VLOOKUP([@[Product ID]],Order_Tbl,3,FALSE),"")

Obtaining Date column

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

Getting Item column

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

outer join in excel with IFERROR and VLOOKUP Functions


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.

Utilizing Power Query Editor

  • Now, click the Close & Load drop-down >> choose the Close & Load To option.

Close and Load queries

  • Later, enable the Only Create Connection option >> hit OK.

establishing connection only

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.

Merge Queries

  • Not long after, follow the steps shown in the animated GIF below.

perfroming left outer join in excel

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

Inserting Table into New worksheet

Ultimately, the final output should resemble the picture below.

Performing outer join in excel with Power Query Editor


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

How to Execute a Full Outer Join in Excel naming tables

  • Likewise, perform the same steps in the “Quantity” worksheet to create the “Quantity_Tbl”.

Establishing only connection for the two queries

  • At this point, go to Get Data >> choose the Merge option from Combine Queries.

Merging queries

  • Now, observe and carry out the steps in real-time according to the GIF below.

performing Full Outer Join in Excel

  • Later, load the data into a New Worksheet as a Table.

Eventually, the output should appear in the screenshot below.

Peforming full outer join in excel

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.

How to Accomplish an Inner Outer Join in Excel

  • Subsequently, Close & Load the transformed data into a new worksheet.

Performing inner outer join in excel

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.

Practice Section for outer join in excel


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


<< Go Back to Power Query Joins | Power Query Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Eshrak Kader
Eshrak Kader

Eshrak Kader is a dedicated professional with a BSc. Degree in Naval Architecture and Marine Engineering from Bangladesh University of Engineering and Technology. He boasts a rich background in Microsoft Office Suite, and over the past year, he has authored over 120 insightful articles for the ExcelDemy. Currently, Eshrak leads a team of 5 in the ExcelDemy Video project and enjoys problem-solving and making videos on Excel. Eshrak’s passion for continuous learning underscores his commitment to excellence in... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo