How to Extract Data from a Spreadsheet using VLOOKUP, MATCH and INDEX

hi welcome to Tut's plus my name is Bob flu sir when you have a lot of data on a worksheet and you want to extract some of that data to use on other worksheets the vlookup match and index functions are great ways to do it these functions work in any version of Excel on Windows or Mac and they also work on the web in Google sheets and also on the web versions of Excel with the vlookup function and the V stands for vertical you have data arranged down columns and by the way there is also an H lookup function where you have data arranged across rows H is horizontal and it works the exact same way as vlookup so there's really no need for me to cover that in this tutorial you can grab actual data from a worksheet like you see here and use it in other sheets where as the match and index functions are really concerned with the positioning of data where your data are located rather than what is the actual value of the data if you want to follow along in this tutorial using your own workbook go right ahead if you'd like to use the sheet that I have here on screen you can see it's called vlookup example you could download this from the Tut's plus website right here on the page where you're watching this tutorial and before we actually start clicking around and writing formulas I want to give you a little bit of info on how vlookup works vlookup links to different tables and they could be on the same worksheet or more likely on different worksheets even in different workbooks using a unique identifier and the unique identifier that we're going to use in this tutorial is going to be an order number but in other situations it could be other things you can think of it sort of like is a serial number if you work with databases you might be familiar with the concept of a primary key so with this table in mind let me explain how the syntax works like any function we start off with an equal sign and then you have the name of the function vlookup and open a parenthesis there are three wired arguments in one optional argument the first argument is what's called the lookup value and that's that unique identifier that we are talking about and in our example that's going to be the order number now because you have to have commas separating the arguments in any function after you put in the lookup value you type in a comma and then the second argument is the table range and in our example that's going to be the entire table but keep in mind it doesn't always have to be that then we put in a comma and the third argument is the column number now let's say we want to get the sale amount and you can see here that's the last column in this table in this instance that's column number eight now don't get that confused with the fact that you can see here it's column H there's no law that says that the table has to start in column a so let's say for example if we start the table in column B then the sale column is still going to be the 8th column but it's going to be in column I instead of column H so that's why we're referring to the actual column number of the data table not the column of the worksheet anyway after you put in the column number you put in a comma and then that optional argument at the end is true or false and this could be a little confusing in this instance we're going to say false and what that means is is this false means don't give me an approximation give me an exact amount true means an approximation is OK now there are times when an approximation is alright but when we're looking at order numbers and when we want to plug in an order number and find the sale amount of that order number we really don't want an approximation and that of course we close the function with a matching parenthesis one other thing because we're going to put this entire table in a formula we need to make sure that we're referring to the table with an absolute reference because we're going to put in the vlookup function and then we want to autofill down that is we want to copy and paste down the formula all the way down a couple hundred rows understanding absolute references is beyond the scope of this tutorial but suffice to say it could be a little messy when you're using an absolute reference in a long formula so what I find it's easier to do is to create a range name when you give a name to a range like to this whole table that we have then you could use that name in your formulas first I'll show you how to create a range name in Excel and it works the same way in Windows or Mac and then I'll show you how to create a range name in Google sheets now the range itself you have a choice you can have it include the column headers or you could have it be just the data below the column headers and the written version of this tutorial I included the column headers so just to be a little different this time I'm going to include only the data and not the actual column headers so I'm going to do is this I'm going to click over here on cell a5 so that's the first cell of actual data and I want to select down and across to the last cell here so in Windows I'll press control-shift end if you're using a Mac you want to press command shift end and you see that select all the way down and across to the last cell now to apply the name I click up here in the name box now not on the drop-down but on the actual box here and when you do that you see that cell reference gets highlighted and I'm just going to call it data you could call it almost anything you want you can't have spaces or dashes in the name though and you're limited to 33 characters so I'll just call this in data and press the Enter key and there it is you can see that data is up there in the name box and we know that works because I can click somewhere over here and then when I click this down arrow and choose data you can see it's selected and again I did not include the column headers creating range names is a little different in Google sheets so here I have the same exact worksheet and you can see I'm in my web browser so I'll click on the same first bit of data we can't select from a cell down and across in Google sheets you have to select first across and then down or first down and then across so I'll press control shift right arrow or if we're on the Mac command shift right arrow and now I'll press control shift down arrow or on the Mac command shift down arrow now that I've done that I go up to the data menu and over here I choose named and protected ranges and then in his box over here I'll call it data and then click done and you can see it's over there and then I'm done with that I can close this little panel with that X so in this tutorial this is the only thing that's different between Google sheets and Excel okay so let's enter some formulas already enough talking right now I'm on the source data sheet let's click on the sale announce sheet and you can see I already have the order numbers and what we want to do is use the order number to look up the sales amount like I was talking about so let's start entering the formulas 8 equals V lookup and if you get that little syntax error you don't have to type the whole thing just press the tab key and it gives us a little bit of syntax helping the look of value we know is that order number so I'll click that and I type a comma now the table array that's the table we had so we don't have to go back to the source data sheet and select all that and make it an absolute reference because we already called it data so all I have to do is type the word data now if you did not create the range name you will have to go to the source data sheet and select the whole thing and make it an absolute reference also Excel understands that oh yes I did create that range name so it's in here I'll show you also a little trick let me just backspace over that let's say I have a lot of range names or I created that range name a long time ago I don't remember what it is I could always press the f3 key on the keyboard and get the paste name box and just double click it and I'll type a comma and then it asked me for the column index number we said before that's column eight that's where the sales figure is put in a comma and here it's even giving us a little syntax help do we want true or do we want false we want an exact match so you could type the word false or you could double click it and it puts it in close a parenthesis and right and it's 40 in fact if we go back here so we're stadia we can see le s md that's correct let's go back here and we want to autofill this so click the cell and if you're not all that familiar with autofill and you notice that there is a little dot in the lower right corner of the cell when you put the mouse corner over the dot the mouse pointer becomes this little crosshair and even if you know autofill here's something that most people don't know you don't have to click and drag this down for 200 whatever res all you have to do is double click the crosshair and it fills in all the way down so you can scroll all the way down and you can see it's filled in all the way that's because there are no gaps in column a if there is a gap somewhere in column a then that autofill will just kind of stop where that gap is if you want to practice this on your own you could go here and maybe to column C or column D and go and use vlookup to find maybe people's last names or the states so let's take a look at the match function we can click the match tab here in the worksheet as I said earlier the match function isn't really concerned with the value of data but location of data actually it's kind of like the game of Jeopardy you tell it what the value is and the match function tells you where the value is kind of if you think of maybe you're walking down the street and you say to somebody oh we're is number 135 and they tell you Oh number 135 is the 4th building down so let's talk a little bit about the syntax of the match function we say equals match open the parentheses right just like any function and then you put in the lookup value that's just like what we were talking about before that's going to be the order number put in a comma and you have the table range table range is the same as what we were talking about before in our case we're going to use the entire table you put in a comma and then you enter the type now what's that type all about when you have that value you're looking for you may have a value that's just above it or just below it and to determine whether or not either of those are acceptable that type you can put in negative 1 0 or a 1 negative 1 means you'll choose the number that's the closest above it if you choose a positive 1 it's the closest number below it if you choose 0 it means you want an exact match also with the match function you have to be aware of whether your data are sorted in any particular order so you have to have data sorted in descending order to use the negative one option you have to have your data sorted in ascending order if you're going to use the positive one option and you can see that's the default or if you don't care if it's in one order or another then you can use a zero since we're looking for only one piece of data we're looking for that order number we need to go and define another range name so let's go back to source data and we're going to be concerned just with the order number so we already have a range name that's for the entire table now we just want a range name for the data going down column a so kind of like what we did before you want to click on cell a5 press control shift down arrow or command shift down arrow on the Mac let's go up here click inside the name box and I'm going to call this order underscore number underscores are okay dashes are not spaces are not okay and press Enter if you're doing this in Google sheets use the same procedure that I showed you just before so let's go back to the match worksheet and we'll put in the function we'll say equals match open the parentheses lookup value we said that's the order number comma the lookup array right that's what we just put in the order number if you want you could press the f3 key and you can see there is the two range names double-click type in a comma and we have our values in ascending order so I'm going to choose the one click it those parentheses and enter it and now we can see that order 10:13 is in the 13th position now if you had included the column headers and this would be showing 14 and we can eyeball this let's go back here and we can see there is order 10 13 if we count from the top that is the 13th one down finally let's talk about the index function and I'm going to click on the index tab of this worksheet the index function is kind of like the opposite of the match function and it deals with two dimensions it deals with rows and columns so we tell it here's the road number and the column number I'm looking for which means the particular cell now return to me the value that's in that particular cell so let's take a brief look at the syntax of the index function we say equals index open the parenthesis first argument is the table range or data range again it's the entire table that we've been looking at put in a comma and then you have the row number put in a comma and then optionally the column number now this is a little weird because the official documentation tells us that column number is optional but it's really the row number could also be optional and what that means is when you have a large table like what we're working with you do want a row number and a column number but let's say you only have single row well then you don't need a column number or if you only have a single column then you don't need a row number so let's put in the function and I'm going to be here on cell c6 so I'm going to say equals index open up the parentheses and just like before the range is the entire data range you can see it finds it for us that we put in comma and we want row nine comma column three close the parentheses enter it and now we can see that that is somebody's last name and let's take a look go back to source data and we can see there it's the ninth row down and third column over if we go back here and let's say just for example maybe I want to go to row 13 and see it's already changing column 4 then I have the state so if I go back over here I can see all right there it is this is row 13 column 4 so having these three functions that can look at a large worksheet and extract value and positioning of data are really great tools this way you can have one sheet that has all of the data in the universe and then individual sheets where you're picking up just those pieces of data that you need and of all of the parts of Excel that people ask me about vlookup is far and away the most common so I hope you found this tutorial helpful once again for a touch plus my name is Bob Blitzer and I'll see you down the road

Channel: Tuts+ Computer Skills

Views: 4,214,432

Rating: 4.8355083 out of 5

Keywords: Spreadsheet (File Format Genre), Microsoft Excel (Software), How-to (Media Genre), Technology, Data, Google Docs (Software), Office

Id: NaBakFrIZkA

Length: 15min 54sec (954 seconds)

Published: Tue Apr 22 2014