How to Combine 2 Excel Workbooks Using VLOOKUP

hello and welcome back to tips and time-savers I'm Danny rocks in today's lesson I'm going to respond to a viewer who asked for my help in combining two Excel workbooks let's take a look at both of the workbooks here's workbook number one notice that there are 26 records and the first field contains a unique identifier the member ID when I take a look at the second workbook notice that there are fewer than 26 records however we're fortunate in that column a contains again the member ID as the unique identifier so knowing that I have both worksheets contain a common field member ID my approach in combining them is to use the vlookup function alright now first order of business is what I want to do when I look at the second workbook it's going to be a lot easier if I make this a worksheet inside workbook number one so the easiest way to do that is to select the worksheet and then R the label for the worksheet and then either right mouse click or in this case I'm going to use one of the menu commands Home tab of the ribbon cells group in the drop-down next to format I want to select move or copy the sheet so in this case I want to keep workbook number two I don't want to delete it so I want to create a copy but I want to move it to another open workbook I want to move it over here to this workbook and I want to move it as the second worksheet in this workbook click OK so now you can see that in workbook number one I have both of the worksheets alright I want to be able to take the information from the third column the fourth column the fifth column 6 and the 7 in other words the information from website city state country and phone fields so I'm going to select the labels I'll use control-c to make a copy come over here into my master worksheet ctrl V to make a copy of them alright I'm going to use the vlookup function the vlookup function has three arguments my first argument is I'm going to look for a match to this cell the membership ID where am I going to look for it I'm going to look for it in an array the array is going to be this group of Records now rather than referring to range a for through G 18 all the time I'm going to use a name range so I'm going to select the range from my array and I'm going to give it a name list 2 so I clicked up here in the name box and I created a name that begins with the letter which is important and contains no spaces my third argument in vlookup is to say in which column is the information so in this case counting from the left website is the third column all right we're ready to use vlookup equals vlookup and I'll use tab and I use ctrl a to bring up the function arguments dialog box so my lookup value is going to be over here in column a in this row now since I'm going to be copying this formula across what I want to do is I want to freeze in place the reference to column a so while the row will change I always want to refer to column a the lookup array remember I created that name range up here so if I have a name range I can use the f3 keyboard shortcut to bring up the pace names dialog box select that name range which is the array and remember for the column index the website was the third column counting over from the left in the second worksheet I'm going to use the fourth argument because I want an exact match I want to match on the membership ID not an approximate match so I'm going to type in false and now I'm good to go click OK and there you go I used vlookup to look for this value now remember I put a dollar sign in front of column a because as I copied this across I always want to refer to column a where did I look I looked in that name range list - where was the information in which column counting from the left the website was in third column in the array and I use false because I want an exact match and not an approximate match so now I can copy this down double click and autofill oh why do I have this ugly n/a are a message well let's take a look here is member ID 105 it exists in the first list but member 105 does not exist over here in the second list that's why we have this ugly and a error message is there a way to remove that yes there is fortunately if you're using Excel 2007 or Excel 2010 there's a wonderful new function that was at it called if error let's see how this works I use ctrl a to open up the function arguments dialog box two arguments for the value the value is going to be the vlookup function or I should say the result of the vlookup function and if there is an error we want to display a blank so the way we do that is double quotation mark space double quotation mark now let's apply this so let's come over here to the first cell that uses the lookup and we want to include the if error function and then the vlookup function as the first argument so after the equal sign type if our left parenthesis here's our first argument use a comma to separate the arguments for our second argument double quotation mark space double quotation mark and of course remember to put a concluding or a matching right parenthesis so now when I click this down you'll see that those n/a Hermus you just go away all right remember that I use the dollar sign to freeze the reference to column a so if I copy this over to pick up the city the city is going to be in the fourth column so if I just make a copy over here I want to change one argument I want to change 3 to 4 the city is in the fourth column again I can copy that down really really simple and I'll do something similar over here I'll change the 4 as the column to 5 so the information for the state is in the 5th column and again I can just double-click and have that copied all the way down notice that there are no na messages so again it's really very simple copy this over and change the five is the column index to six double click and finally copy this over and make one change I'm going to change the six to seven and again I'm going to double click and there you go really really really very very simple now notice over here I have some zeros and things like that not I don't have the formatting that I had in the original the way that I got the formatting is I use the special formatting so I've selected the records for the phone field I'm going to click on the more button over here and what I want to do on the number tab is come down here and click special and I want to use this mask with the phone number so now click OK so you see that I didn't have to add in the parenthesis for the area code and the dashes for the phone number it was done with formatting so really really very simple vlookup is a it's an essential function to learn if you're going to be a master of Excel and I use the new if error function so that any error messages that would display because I didn't have a matching record now display the blank so the if our vlookup and then I use double quotation mark space double quotation marks so there you go now you've learned how easy it is to combine two lists we use vlookup because we had a unique identifier member ID in both lists and I'll look for you in the next lesson

Channel: Danny Rocks

Views: 1,387,865

Rating: 4.8317976 out of 5

Keywords: VLOOKUP Function, IFERROR Function, Move or Copy Sheet, Mixed Cell References, Excel Tutorial, Software Training, educational

Id: 809m6kLTfgI

Length: 8min 21sec (501 seconds)

Published: Mon Aug 13 2012