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.

What is the best/easiest way to combine data from two different Excel spreadsheets into a third Excel spreadsheet? Spreadsheet 1 will have the username only. Spreadsheet 2 would have lots of information including first name, last name, department, username, etc. What is needed is for the third spreadsheet to display the usernames from Spreadsheet 1 with the first name, last name, and department from spreadsheet 2.

share|improve this question
2  
if the username only is in one sheet, what common characteristic is there between the usernames and what is in the other sheet? is the username in both? –  datatoo Jan 24 '13 at 19:23
1  
+1 to @datatoo - You need a per-user unique identifier that is common between both sheets. i.e.: If the first sheet only has usernames and no other data at all, than the second sheet must also have usernames alongside the correlating data. Otherwise, if there is also some other unique identifier on the username sheet (e.g.: employee number, e-mail address, etc.) which is also on the "lots of information" sheet, you could use that instead. Absent a common, per-user unique identifier though, you'll be out of luck. –  Iszi Jan 24 '13 at 20:02
1  
Sheet 1 has only usernames, but the same information is in sheet 2. I want the rows containing the same user names found in sheet 1 and sheet 2 to be pulled out and displayed in a 3rd sheet. –  CSF Jan 24 '13 at 20:18

3 Answers 3

The function you want to use is VLOOKUP. How you do this will depend slightly on how your sheets are arranged, but all will follow the same syntax:

=VLOOKUP(lookup value, table array, column index number, range lookup)

  • lookup value is the data you want to search for.
  • table array defines the cells you want to pull data from, including the column containing your search target
  • column index number is the index of the column within table array from which you want to pull information. (e.g.: For an array of A:E, column D would be 4.)
  • range lookup is a TRUE/FALSE option to specify whether an approximate match is acceptable, or an exact match is needed. To keep things simple, I always set this to FALSE. Press F1 in Excel if you need more details.

The format of your formula will differ just a bit depending on whether your data is all in the same workbook or not. I'll give examples for each below.

IMPORTANT NOTE: The search term you're using as a lookup value must be findable in the first column of table array for VLOOKUP to work.


First Example: All data is going to be in the same Excel Workbook, but on different sheets. The first sheet is labeled "Usernames" and contains just the usernames. The second sheet is called "User Data" and contains all the user details. We'll call the third sheet "Lookup Results". The "User Data" sheet contains five columns, A:E.

  1. Make sure the "User Data" spreadsheet has all the usernames in column A.
  2. Copy all the usernames from "Usernames" to "Lookup Results".
    • I'm going to presume you're using a header row, so the first username on "Lookup Results" will land in A2.
  3. Formula for B2 on "Lookup Result" should be: =VLOOKUP(A2,'User Data'!A:B,2,FALSE)
  4. Formula for B3 on "Lookup Result" should be: =VLOOKUP(A3,'User Data'!A:B,2,FALSE)
  5. Formula for C2 on "Lookup Result" should be: =VLOOKUP(A2,'User Data'!A:C,3,FALSE)

You should see the pattern here by now. For each column, you should be able to just write the VLOOKUP formula in the first cell (e.g.: B2), then populate the formula down the rest of the sheet. However, cut-and-paste of the formula across columns is not so straightforward - you need to update the table array and column index number values.


Second Example: Each data set is kept in its own Excel Workbook. The sheet names within the workbook are default (i.e.: The first sheet is "Sheet1"). The workbook file names are "Usernames.xlsx", "User Data.xlsx", and "Lookup Results.xlsx". These are all in a folder called "My Spreadsheets", which is on the Desktop of a user called "Me".

  1. Make sure the "User Data.xlsx" sheet has all usernames in column A.
  2. Copy all usernames from "Usernames.xlsx" to "Lookup Results.xlsx".
    • Again, presuming you use a header row, this will start in A2.
  3. Formula for B2 in "Lookup Result.xlsx" should be =VLOOKUP(A2,'C:\Users\Me\Desktop\My Spreadsheets\[User Data.xlsx]Sheet1'!A:B,2,FALSE)
  4. Formula for B3 in "Lookup Result.xlsx" should be =VLOOKUP(A3,'C:\Users\Me\Desktop\My Spreadsheets\[User Data.xlsx]Sheet1'!A:B,2,FALSE)
  5. Formula for C2 in "Lookup Result.xlsx" should be =VLOOKUP(A2,'C:\Users\Me\Desktop\My Spreadsheets\[User Data.xlsx]Sheet1'!A:C,3,FALSE)

Again, you should be able to see the pattern here by now. Cut/paste/tweak as needed down the rows and across the columns, and you're set.


One other thing to keep in mind here is that this sheet will not be automatically updated for changes in the "Usernames" data. Changes to the "User Data" sheet can be pulled with this method, but more advanced techniques will be needed if you want to follow changes in "Usernames" as well.

share|improve this answer

HLOOKUP/VLOOKUP - use a formula to fetch the user name from Spreadsheet 1, then use the username as key and spreadsheet 2 as a lookup matrix for one instance of HLOOKUP/VLOOKUP (I don't know which one is which, as I am using a non-english excel version).

share|improve this answer
    
So just to clarify, I have about 50 users names (rows of data) in sheet 1 that I need to see if they exist in sheet 2, which contains about 12,000 rows of data. I want to to get Excel to display the actual data found in the matching row - not just TRUE or FALSE as the result on a third sheet. Still not sure how to get started. –  CSF Jan 24 '13 at 20:33
    
First, fill the first 50 cells in column A of sheet 3 with simple references to the corresponding username-containing cells on sheet 1. Now you have your usernames in sheet 3. Then, assuming the full name is stored in column C of sheet 2 and you want that in column B of sheet 3, fill B1 of sheet 3 with a formula like =VLOOKUP(A1;Sheet2!1:1048576;3;FALSE) - read the documentation of VLOOKUP for details. –  Jan Schejbal Jan 24 '13 at 20:41

You can use Query from Excel Files :

  • Define name for the dataset in Spreadsheet 1 (Formulas tab -> Define name)
  • Define name for the dataset in Spreadsheet 2
  • While in Spreadsheet 1, go to Data tab, select "From Other Sources", and from the dropdown, select "From Microsoft Query"
  • Select the other spreadsheet file and confirm that you want to merge the columns manually
  • In the following window "Query from Excel Files", drag&drop the 'username' column of first dataset into the 'username' column of second dataset - a link between these columns will be created
  • Go to File menu, click "Return Data to MS Office Excel", an Import Data dialog will pop up
  • Select the sheet into which you would like the matched data to be imported
  • Click OK - you should see matched data with columns from both spreadsheets

Or if you don't mind uploading your files to an online service, you can use for example http://www.exmerg.com/data-merging and merge the sheets using drag&drop (Disclaimer: I am author of the tool).

Hope this helps.

share|improve this answer

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.