Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

share|improve this question
    
I find the following page very usefull: randomwok.com/excel/how-to-use-index-match – Tommy Bravo Mar 4 '14 at 14:19
    
randomwok.com/excel/how-to-use-index-match => especially the easy "reminder" at the end: =INDEX ( Column_I_want_a_return_value_from , ( MATCH ( My_Lookup_Value , Column_I_want_to_Lookup_against , 0 )) – Tommy Bravo Mar 4 '14 at 14:25
up vote 103 down vote accepted

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
share|improve this answer
9  
+1, wish I could +more. This question needs to come up first on Google, I spent way more time working on this than I wanted to! – Crisfole Jun 13 '12 at 13:37
1  
Is there a way to avoid hardcoding the path? Would relative path work? – Rekin Nov 14 '12 at 12:57
    
i have no idea :( if google doesn't either, try to ask it in a new question.. – Aprillion Nov 16 '12 at 1:05
16  
As an added bonus, Microsoft Query lets you experience 16-bit-style Common Controls for opening the Excel file and for message boxes. That way you can remember when you were young. :-) – Edward Brey Dec 8 '12 at 5:10
1  
This is going to increase my productivity in a major way. Thanks! – Hack-R Jun 4 '15 at 14:43

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.

share|improve this answer

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

share|improve this answer
    
very usefull for sheets on the same workbook (but i find INDEX+MATCH functions even more usefull), a little more complicated when updating data from closed external workbooks... – Aprillion May 7 '12 at 9:51

You can't preform SQL style joins on Excel tables from within Excel. That said, there are multiple ways to accomplish what you are trying to do.

In Excel, like Reuben says, the formulas that will probably work the best are VLOOKUP and HLOOKUP. In both cases, you match on a unique row and it returns the value of the given column\row to the left\down from the found id.

If you only want to add a couple extra fields to the second list, then add the formulas to the second list. If you want an "outer join" style table, then add the VLOOKUP formula to the first list with ISNA to test if the lookup was found. If Excel's Help doesn't give you enough details on how to use these in your particular instance, let us know.

If you prefer to use SQL then link the data into your database program, create your query, and export the results back to Excel. (In Access you can import Excel Worksheets or Named Ranges in as a Linked Table.)

share|improve this answer
11  
actually, you can - it's called Microsoft Query – Aprillion May 7 '12 at 9:31

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.

share|improve this answer

For Excel 2007 User: Data > From Other Sources > From Microsoft Query > browse to the Excel file

According to this article, querying from XLS version 2003 might result a "This data source contains no visible tables." error because your worksheets are treated as SYSTEM table. So check the "System Tables" in the "Query Wizard -- Choose Columns" dialog's options when you create the query will work round.

To define your join: Microsoft Query dialog > Table menu > Joins...

To return data to your original Excel sheet, choose "Return data to Excel Sheet" from Microsoft Query dialog > File menu.

share|improve this answer

At XLTools.net we have created a good alternative for MS Query to work especially with SQL Queries against Excel tables. It is called XLTools SQL Queries. It is a lot easier to use rather than MS Query and works really well if you just need to create and run SQL - no VBA, no complex manipulations with MS Query...

With this tool you can create any SQL query against tables in Excel workbook(s) using embedded SQL editor and run it immediately with the option to put result on a new or any existing worksheet.

You can use almost any type of join including LEFT OUTER JOIN (only RIGHT OUTER JOIN and FULL OUTER JOIN is not supported).

Here is an example:

XLTools SQL Queries - Query Builder

share|improve this answer

If you're familiar enough with databases, you could use SQL Server to connect both worksheets as Linked Servers and then use T-SQL to do your back-end data work. Then finish by connecting Excel back to SQL and pull the data into a table (regular or pivot). You can also consider using Powerpivot; it will allow joins between any database sources-including Excel used as flat databases.

share|improve this answer
    
Seems like a very roundabout way of doing this.. – Thihara May 4 '12 at 17:00
2  
You're right, but the initial question is based upon using a spreadsheet (or two) to do a database function, so I'm not sure of any really efficient way to accomplish the task. – dav May 7 '12 at 11:54

Looking for the same problem I came across RDBMerge, which I think is a user friendly way to Merge Data from Multiple Excel Workbooks, csv and xml files into a Summary Workbook.

share|improve this answer

protected by Community Nov 24 '14 at 16:49

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site.

Would you like to answer one of these unanswered questions instead?

Not the answer you're looking for? Browse other questions tagged or ask your own question.