4

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 e.g. 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?

Robotnik
  • 2,645
CSF
  • 41

4 Answers4

6

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.

Iszi
  • 14,163
1

Vlookup and similar will only work if the data in both sheets is identical.
I believe what you need is the fuzzy logic add-on for Excel. This will allow you to find similar results based on several different parameters. Check the download page.

kenorb
  • 26,615
SiParker
  • 51
0

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).

Jan Schejbal
  • 1,132
0

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
sevce
  • 249