Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

I want to use the 2nd list to filter the first. 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 konwing 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 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 at 14:25
add comment

6 Answers

up vote 47 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. edit the Data > Connections > choose your new connection > Properties > Definition > Command text

you can now edit this Command text as SQL, i tried implicit joins, "inner join", "left join" and unions:

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
1  
+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! –  Cpfohl Jun 13 '12 at 13:37
    
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.. –  deathApril Nov 16 '12 at 1:05
4  
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
add comment

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... –  deathApril May 7 '12 at 9:51
add comment

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
6  
actually, you can - it's called Microsoft Query –  deathApril May 7 '12 at 9:31
add comment

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
    
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
add comment

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
add comment

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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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