How do I join two worksheets in Excel as I would in SQL?

I have two worksheets in two different Excel files. They both contain a list of names, id numbers, and associated data. One is a master list that includes general demographic fields, and the other is a list that only includes name and id, and an address. This list was pared down from the master list by another office.

I want to use the 2nd list to filter the first. Additionally, I want the results to include other fields from the master worksheet alongside the address fields from the second worksheet. I know how I could do this very easily with a database inner join, but I'm less clear on how to do this efficiently in Excel. How can join two worksheets in Excel? Bonus points for showing how to do outer joins as well, and I would greatly prefer knowing how to do this without needing a macro.

Answer

For 2007+ use Data > From Other Sources > From Microsoft Query:

1. choose Excel File and select your 1st excel


2. choose columns
   (if you don't see any list of columns, make sure to check Options > System Tables)


3. go to Data > Connections > [choose the connection just created] > Properties > Definition > Command text

You can now edit this Command text as SQL. Not sure what syntax is suported, but I tried implicit joins, "inner join", "left join" and unions which all work. Here is a sample query:

SELECT *
FROM `C:\Users\Peter\Documents\Excel-to-excel\Source_1.xlsx`.`Sheet1$` a
LEFT JOIN `C:\Users\Peter\Documents\Excel-to-excel\Source_2.xlsx`.`Sheet1$` b
ON a.col2 = b.col2