Excel VLOOKUP With Multiple Workbooks

alright hello everybody this is Maxwell Stringham your trusty tech trainer here to help you out with some vlookup functionalities specifically how to use vlookup when you are using more than one Microsoft Excel workbook in order to download and follow along by utilizing the Excel workbooks that I'll be showing you today in this video you can go ahead and go to this shortened URL and download the two Excel workbooks that are in the Google Drive folder that is available through this link it's again this is a case sensitive shortened URL so anything that's capitalized must be capitalized anything this lower case must be lowercase we'll be using these two files at this abbreviation for employee deductions and this one for job salary records so with that let's get going now we have two folders or two Excel workbooks here we have job salary records and we have this employee deductions worksheet now with these two whenever I'm working with - oops excuse me whenever I'm working with two Excel workbooks I like to be able to see them both side-by-side so first step that we're going to want to do is click view and then click arrange all after we do that we go to vertical and then click OK now what that did is that just arranged the two Excel workbooks side-by-side with the to arrange them side-by-side into vertical columns now to go ahead and get started on our goal our objective is to create a new column titled go ahead and type it in here it is going to be titled annual salary and this is in column o of the employee deductions workbook now in order to populate this we will use the vlookup functionality so in order to get started with our V lookup function we need to go ahead and start with an equal sign as we always use an equal sign to start any function in excel so equal sign first step then I type the lookup vlookup stands for value lookup now Y value lookup well because the first step of this function is because we are looking up a value this here that pops up underneath is sort of like a display or a legend of how the vlookup function works so lookup value now what we are going to indicate for this first field of our function is what value do we want to look up well we want john smith's annual salary now we can't just look up his salary straight up but we know that his salary is listed in another folder in this other workbook and that his salary is listed relative to his employee ID number so what we're going to do is look up his employee ID number in the other workbook so I selected John Smith's employee ID number a two as I am working in Row two and I want to populate this part of this column Row two of this column so a 2 comma two move on to the next step table array now table array basically this is the part of the function where we tell the computer hey go ahead and look in this array or this region of the table for the value that I just told you to look up so we don't want to look in this same workbook this is the employee duck deductions workbook where we want it to wind up instead we're actually going to click over here and activate this one we want to highlight or not highlight select this entire array of this table all the values in this table as this includes all of those employee ID number and also includes the annual salaries that are relative to those employee ID numbers now I click back over to this sheet and I can see here that the job salary records excel sheet is titled in the single parentheses and then the exclamation mark finishes the title of that worksheet basically that's the part where it tells the workbook hey or that's the part of that function excuse me within this part of the function is basically saying before that exclamation mark hey go over to this other file pull the array from there this right here is the relative coordinates of that array now we hit comma to move on to the next column index number now the column index number is basically going this is where we indicate in our function where within the array that we just selected so again within the array that we just selected within which column of that array is the data that I want to pull so one last time within the array that I just selected in which column of that array is the data that I want to actually pull now we already know that we want to match it to the employee ID number that we have listed in 82 of this file we know the arrays now we know the array selected now now column index number so we jump over to here to pick our column now unfortunately in Excel columns aren't actually already indexed by number columns are indexed by letter so this may seem a little problematic but Excel makes this easy on us by simply making it so that a would be indexed as a 1 B as a 2 C as a 3 D as a 4 and so on so forth in intervals of counting up by one so with that we would just count over 1 2 3 4 5 6 seven eight and nine so column I if it were indexed as a number would be indexed as number nine column number nine so I go back over to here and hit the number nine then I go ahead and put a comma because I'm finished with that part of my vlookup function now this is the final and easiest part of the function if we select true approximate match then what this does is basically is telling the function hey I don't really care if the match of the data you pull is exact it can just be approximate now we don't want that again if you select true approximate match it might just give you some rounded numbers some numbers that are a little bit off but we don't want that we want false exact match so in order to select that we need to double-click false exact match and then I hit the parentheses close the parentheses brace and hit the enter key and voila just like that this cell has now been populated with John Smith's salary of fifty four thousand four hundred fifty four now in order to apply this function in a relative manner to the rest of the people on or in this sheet we can go ahead and apply it to them by placing our cursor over the corner of the bottom right corner of this cell that we have selected so again select the cell that already has this function already written out in it place the cursor over the bottom-right corner so that the cursor indicator turns to a solid black plus sign and then click and hold and drag it all the way down to the bottom of where you want it to be or the bottom of where you want to finish applying this function in a relative manner now when I say applying it in a relative manner that means is that everything that how I indicated in this guy John Smith I said to look at a 2 for his employee ID number look in column a of this row of Row 2 then I'm saying all right then for this guy who's down in row 73 I'm saying well go look at column a of row 73 as you can see here in the vlookup up top same thing goes the array is still the same the table array is still selected the same and the column index number is still the same because I'm still pulling his salary information from the same part of the same excuse me the same column of the other table and I am also still indicating false because I want that value to be exact so with that we are almost finished as you can see these values are not formatted the same as the number values over on this other table to the right so in order to fix that we can select them by again placing our right cursor or placing our cursor over the bottom-right corner of the selected cell and Reese electing all of these again and then we go to Home tab click number two change our number formatting this is where we go to format numbers and we can choose dollar signs percents etc however we don't want to just do dollars we don't just want to set this as a money format because this is not just formatted as dollars and cents instead we want to make sure that we are exactly the same so we go to again that was a number and then under the number tab here we go to the number category for number formatting then decimal places we choose to show two decimal places or to show the decimal two places to the left of the last digit or integer to show the hundreds I always show hundreds and then select to use the thousands separator with a comma click OK and now voila we are finished it is exactly the same every person salary has been populated in this list exactly the same as they are in this list however it's not just a simple copy and paste they have been populated based on the vlookup formula which look them up relative to their employee ID number that is all if you have any questions please do contact me in the comment section below and I am always here to help you

Channel: Maxwell Stringham

Views: 2,468,542

Rating: 4.722764 out of 5

Keywords: Excel, VLOOKUP, Multiple, Workbooks, Sheets, Edtech, How to

Id: gCvlAtUIaZc

Length: 12min 13sec (733 seconds)

Published: Fri Mar 18 2016