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

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

Support the accepted answer. I just want to emphasize on "choose columns (if you don't see any list of columns, make sure to check Options > System Tables)"

Once you select the excel file, very likely you will see this data source contains no visible tables prompt, and the available tabs and columns are none. Microsoft admitted that is a bug that the tabs in the excel files are treated as "System Tables", and the option for "System Tables" is not selected by default. So don't panic at this step, you just need to click "option" and check "System Tables", then you see the columns available.

VLOOKUP and HLOOKUP could be used to search for matching primary keys (stored vertically or horizontally) and return values from 'attribute' columns/rows.

You can use Microsoft Power Query, available for newer versions of Excel (similar to the accepted answer, but much simpler and easier). Power Query calls joins 'merges'.

The easiest way is to have your 2 Excel sheets as Excel tables. Then in Excel, go to the Power Query ribbon tab, and click the 'From Excel' button. Once you have imported both tables into Power Query, select one and click 'Merge'.

While I think Aprillion's answer using Microsoft Query is excellent, it inspired me to use Microsoft Access to join the datasheets which I found much easier.

You need to have MS Access installed of course.

Steps:

• Create a new Access database (or use a scratch DB).
• Use Get External Data to import your Excel data as new tables.
• Use Relationships to show how your tables are joined.
• Set the Relationship type to match what you want (representing left join etc.)
• Create a new query which joins your tables.
• Use External Data->Export to Excel to generate your results.

I really couldn't have done that without Aprillion's great answer.