Excel tip - How do I merge data from one sheet into another

So you have a spread  sheet with a large amount of data in it and you have done a fair amount of work manipulating the data when you realize that you are missing some info. 

You are able to get the missing info from another spreadsheet but don't want to have to start your data manipulation over again.  Well, you are in luck.

As long as you have a common unique field in both spreadsheets you will be able to use VLOOKUP() to merge your data.  Here is how:

VLOOKUP is an excel function that takes 4 (or optionally 3) arguments (or pieces of info that you give it).

The format will look like this: =vlookup(lookup_value, table_to_search, column_number_returned, exact_or_not)

Now let's break it down so it makes sense.

In this example will be taking baseball player names and teams from one sheet and adding the position of the player from a second sheet that has the players name and position.  The screen shots have a very small amount of data in it but if you could imagine having hundreds or thousands of players to compare how much time this will save you.


To start we have this data:

 

And in our second spread sheet we have this:



In the table you would like the new data merged into, add a column and give it a header row.  Put your cursor in the second row and click on the arrow next to the auto sum button and type vlookup in the search field.









Now in the pop-up that takes the function arguments put your cursor in the Lookup_value field and then place your cursor in the first cell in your spread sheet that you will try to match up.  In this example it will be in the sheet that has the names and teams of the players and I will put my cursor in cell A2 (where it says "Cabrera".)  Or you can type your cell number (A2).

You should see the value populated to the right of the lookup_value field.  Now you need to define where excel will look for this info.  In this example we can now place our cursor in the Table_array field and click over to our second sheet. Excel populates the sheet name for us.  We will now highlight ever row starting with the column that has our unique values and ending with the field we are going to try to add to the first sheet.  In this example we only have two columns so I will select both but you may have columns in between the two fields you will be using and if that is the case just select the ones in between as well.  You will do this by clicking on the letter in the top of the column and dragging over until you have highlighted all the columns you need.  In my example excel populates the Table_array field with "Sheet2!A:B".

Now you need to tell excel what column to return when it finds a match.  In this case it will be the 2nd column so I type a 2. If you have columns in between your search column and the column that contains the value you would like to return simply count the columns (starting with the search column and ending with the return column) and enter that number.  Example: If your search column is column D and you are returning values in column H your previous argument would say "Sheet2!D:H" and for this argument you will want to put a 5.

The last argument is optional and has a funny name of Range_lookup. This argument takes either the value "TRUE" or "FALSE" and determines if excel should look for an exact match or not.  FALSE will find an exact match and True (or leaving it blank) will find the closest match in the search column.  If you are working with numbers or currency you can use TRUE if you would like to return the closest match, but keep in mind you want to have your tables sorted by the value you are searching on because it will pull in the next value it finds and if they are out of order you returned values can be very wrong.  With text searches I would think that you would want an exact match, so use the FALSE value here.

So our finished pop-up window looks like this:



You can see that the value "Outfield" has been correctly found indicating that my formula is correct.  Keep in mind that this may return #N/A if the value you are searching for doesn't exist in your Table_array.

Click OK and you will see the value in your 1st sheet.  Click the box with your formula, hover over it and you will see a small black box in the bottom right hand corner of that cell. If you hover over that little black box your cursor will change to a denser, smaller cursor and you can then double click to populate all of your formulas.

Our final results look like this:



You can see that a few values were not found (I left them out on purpose) and those are returned as #N/A.

If you want to, you can now selected everything in the row, copy, and right click and select Paste Special.  Choose Values and click OK.  This will replace your formula with the values that your formula returned and it will be as if your data was there all along.

I hope you find this helpful.  I know I use it all the time and it is a real time saver.  The first few times I used vlookup it took some getting used to but I assure you that if you use it a few times it will be second nature. Good luck.