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.
The function you want to use is
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
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,
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
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".
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.
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).
You can use Query from Excel Files :
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.