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.