How to simulate a full outer join in Excel?

Easy approach - standard Excel operations

First, copy/paste both key columns from both tables into a single, new sheet as a single column.

Use the "Remove Duplicates" get the single list of all your unique keys.

Then, add two columns (in this case), one for each of your data columns in each table. I recommend you use the format as table option too as it makes your formulas look much nicer. Using vlookup, use the following formula:

=IFERROR(VLOOKUP([@ID],Sheet4!A:B,2,FALSE),"")

Where Sheet4!A:B represents whatever the source table data table is for each respective value. The IFERROR prevents the ugly #N/A results which appear when vlookup is not successful and in this case return a blank cell.

This gives you your resulting table.

Sheet3:

Sheet4:

Result data:

Result formulas (Ctrl+~ will toggle this):

Built in SQL Query

You can also do this with the built-in SQL query. It's... much less user friendly, but maybe will be a better use case. This will likely require you to have formatted your "source" data as tables.

1. Click on a cell in a new sheet
2. Go to Data --> From Other Sources --> From Microsoft Query
3. Select Excel Files* under the Databases tab and hit ok
4. Select your workbook
5. Select the following four fields:
   • 
6. Click "next" and "ok" at the nice 1990s formatted warning you see
7. Following these instructions create the first Left Outer Join. In my case I am using the "countries" table as the left source and the "names" as the right.
   • 
   • This only gives some of the rows (since you join on the ID)

8. The "create a subtract join and then add it as union" part is more complicated..

   • Here is the subtract join configurations:
   • Copy this join's SQL from the SQL button:

   • SELECT countries$.ID, countries$.Val1, names$.ID, names$.Val2 FROM {oj C:\Users\Username\Desktop\Book2.xlsx.countries$ countries$ LEFT OUTER JOIN C:\Users\Username\Desktop\Book2.xlsx.names$ names$ ON countries$.ID = names$.ID} WHERE (names$.ID Is Null)

9. Go back to the first outer join you created. Manually edit the SQL and

   • add Union to the bottom
   • Add the above subtract join text to the bottom of the join
10. Hit the "Return Data" button immediately to the left of the SQL button
    • You may want to edit the SQL to only select the specific data you want at this point. I find it easier to hide columns in the result.
11. Place the Query somewhere and confirm it's location
    • 

Not for the faint of heart. But if you want a great chance to see some not-updated-as-long-as-you-might-have-been-alive parts of Office it's a great chance.

As an alternative solution, may I suggest Power Query? It's a free Excel add-in from Microsoft for basically performing exactly this sort of thing. Its functionality will actually be directly included into Excel 2016 as well, so it's futureproofed.

Anyway, with Power Query, the steps are pretty simple:

1. Import both tables as queries into the Power Query Editor.
2. Perform a Merge Queries transformation on them, setting the appropriate join column and setting the join type as Full Outer.
3. Load your result table into a new sheet.

The nice thing about this is once you've set this up, if you make changes to your base data tables you just hit Data > Refresh All and your Power Query result sheet gets updated as well.