Advanced Excel - VLOOKUP Basics

In this advanced Excel tutorial, I'm going to show you the basics of using the VLOOKUP function. And VLOOKUP can really be quite a powerful tool for you in Excel. You can do several different things that can be useful. But in this tutorial, like I said, we're going to look just at the basics of how to use it. And in this tutorial, I'm using one of the spreadsheets that I made in past tutorials. If you haven't seen my Excel Basics tutorial, my Intermediate Excel tutorial, and also the series of advanced Excel tutorials, you really should go back and watch those. But in those tutorials, I created this spreadsheet that's a movie inventory of movies that I have. And it's quite a short list. But for the sake of this tutorial, let's imagine that there are 1,000 or 2,000 or 5,000 records here in this spreadsheet. Instead of just 20 or 30 movies, let's pretend that there's 2,000 movies. And I'm also going to have you pretend that instead of just six or seven columns of data, imagine that there's 20 different columns. You know, one of the columns lists the duration of the movies. Another one lists the star of the movie. Imagine just a pretty big spreadsheet with lots of columns and lots of records, lots of rows. One of the challenges that we have when you get a huge spreadsheet like that is how do I pull out the data that I want to see and that I want to think about from a spreadsheet? And I'm going to show you how to use VLOOKUP to do that. So one of the best ways to think about VLOOKUP is to think of it as being similar to looking in the index of a book to find out what is in the book, or looking through a catalog to look up something that you might want to buy. And you can find the part number, you can find the cost, and things like that. Another comparison would be that it's similar to looking up someone in a phone book. You look up their name and you find their address or their phone number. So in this spreadsheet, again imagining that it's much bigger than it actually is, let's say that because it's so big I find it hard to know what the rating is of a particular movie. And I would like to be able to type in the name of a movie and find out exactly what the rating is for that movie. To do that I'm going to need two cells. And so I'm going to use these two cells here. And I'm going to go to this cell and I'll type in equals VLOOKUP. And notice as soon as I do that I get a pop-up that is going to hopefully help me to do this formula correctly. Now I'll put in the left parenthesis and it gives me some more details. It says that I need to now put in a lookup value, a table array, a column index number, and a range lookup. And each of those, notice, is separated from the rest with a comma. So lookup value comma, table array comma, column index number comma, range lookup. Now the official name for each of these four different sections of this formula is argument. Each of these is an argument. So this is argument number one, two, three, and four. So let's look at argument one first. It's asking for the lookup value. So basically what is it that I know? What is it that I'm going to look up information about? But it's something that I already know. So again think of going to a library. You know the name of the book that you want. Let's say it's the book A Wrinkle in Time. It's an awesome book written for young adults, but I think it's great for anyone. And I hear Disney is soon to make it into a movie. But anyway, let's say at the library you would like to look up where to find that book. What you know is the name of the book in that case. It's A Wrinkle in Time. So that's the lookup value. Now in this case, I would like the lookup value to be changeable. I would like to be able to type in, let's say, the Princess Bride and have it tell me PG. Or the Martian and have it tell me PG-13. And so because I want it to be changeable, I'm just going to click on cell H8. I'll click there. So basically whatever I type in cell H8, that will become the lookup value. All right, I'll put a comma in there. Next, it wants the table array. And if you've watched my Excel Basics tutorial, you know the term range. And that's basically what this table array is. It's a range of your spreadsheet that you want Excel to look at. And to be careful, I really want to include all of the columns that I have here. Now you may not want to when you do this yourself. But I want to go in and choose A3 through F22. So that'll be all of the actual data from my spreadsheet. It's not going to include some of this other stuff that I put in later. So to put that range into the formula, all I have to do is click on it and drag to select that range that I want it to look through. Now that's really easy when it's such a small spreadsheet. But imagine a giant spreadsheet like I asked you to pretend that this is. In that case, it wouldn't be practical to click and drag. So that's OK. You could just go in and type A3 through, and that's indicated by a colon, F22. OK, great. So I've done the first argument and the second argument. Now on to the third argument. The third argument is it wants to know a column index number. So what this is asking for, it's asking for you to pick one of the columns. Now it's not looking for column A, column B, column C. No, in this case, it's not. What it's looking for is whatever the table array is that you selected, whatever that first column in the array is, that's number 1. Then number 2, number 3, number 4, number 5, number 6. Now what if I hadn't included column A in the table array that I selected? What if I had started here in B3 instead of A3? Well, then this would be column 1. This would be column 2. So it doesn't have to correlate to the A, B, C, D, E, F. It can just be whatever it is that you selected for the table array or you clicked and dragged to highlight. The far left column is column 1, then column 2, then column 3, then column 4, then column 5. So now I'm going to do that argument. I'll put in a comma to separate the second argument and the third argument. And how do I know which column to select? Well, the column I select is the column that has the information that I want to draw out. So if you remember my example, I want to be able to type in the name of a movie and find out what its rating is. So that means I want column C. But remember, I've got to put it as a number. So I go to the array, the table array that I highlighted, and I say, OK, this is the first column. This is the second. So I want the third column. So I type in 3 comma. Next, it wants a range lookup. And this is kind of tricky and a little confusing for me as well as for many people. Basically, what this is asking is, is there a requirement that H8 be an exact match to what's in the spreadsheet? And this is mostly useful, I think, with numbers. So for example, let's say instead of typing in names of movies, what if we typed in the date that the movie was purchased? If I were to type in 2007, it would produce a PG. But what if I typed in 2006? And what if there is no 2006 entry here? Well, if I, in this fourth argument, in the range lookup, if I have it set to be true, then it will look for an approximate match. It'll find basically whatever is the closest. If I type in the word false, then it's going to require an exact match. If you don't type in the exact match, it's not going to produce the result that you're looking for. In this case, I'm going to leave it as false. So I'm going to require the exact match. And then you're supposed to put in the right parenthesis, but you don't really have to. So I'll just type enter. And look, it's giving me a message saying the value is not available. So this is an error. Now, the reason it's showing me an error code is because there is nothing typed in this cell here. So let's test it out. I'm going to type in, I am legend. Let's see what happens. Look, it says PG13. So that seemed to work pretty well. Let's try it again. I'll type in Groundhog Day. So I type in Groundhog Day, hit enter, and it's PG. Now, once you have done this, if you've set up a VLOOKUP like this, if you regret how you've laid it out on the screen like I do a little bit, let's see what we can do to make it so that you type the word here and it tells you on the right whether it's PG. I think that would look a little better. So to do that, I'm just going to double click here and I can see up here in the formula bar, that's the formula. I'll just copy that and then I'll paste it here. And notice that it gives me an error again. So I need to double click on it and look at my formula and see where it messed up. Because I copied it from one column to the next, look, it shifted everything to the right a little and also down because I moved down one. So I can repair this formula just by clicking on the first argument, clicking and dragging to highlight it. You can also do this up here in the formula bar and then I'll click on H8. Next, we go on to the second argument, which is the table array. The table array was changed quite a bit. I'm just going to click and drag. But remember, with a larger spreadsheet, you would need to just type in the cell names. That would be much faster. And the column index number is still valid, three. And the range lookup, false. So now I can hit return or enter on the keyboard and I can actually delete the one that's above. So now I have a space to type in the movie name and it tells me the rating off to the right. Okay, let's do another example just to test it out. I'll type in The Giver and it changes to PG-13. So I hope that you found this tutorial to be helpful. This is just the beginning of what VLOOKUP can do for you. But to be honest, this is how I use it most often, just as a way to create kind of like a form that I just fill out. I type in the name of something or the part number of something and then it tells me other data. So like I said at the beginning, it's kind of like looking up a book at the library and finding information about the book or looking up somebody's name in the phone book and getting their address or getting their phone number or the city that they live in. Now if I wanted to, of course, I could highlight these and I could change them to be bold and centered and things like that and kind of create a nice little space here that it's a tool basically that I can click and type in to get the information that I'm looking for. Now there are a couple of rules you need to know. If you're going to use VLOOKUP, probably the first rule is that you need to have your data in vertical columns. So I have column titles here and the lists of items go vertically, right? Information about those items in the list go horizontally, but the actual different records go vertically in columns. It has to be that way. And the reason why is because VLOOKUP, the V in VLOOKUP means vertical. So if you have your spreadsheet set up the other way, it wouldn't work. There is such a thing as HLOOKUP for horizontal, but anyway, that's rule number one, that you need to have your data arranged vertically in the spreadsheet, in columns. The second rule is that the information that you know, that you're going to be typing in the box basically, has to be to the left of the data that you want to find. So the data that I know in this case is the names of the movies, right? That's what I'm going to be able to remember and produce and type in this box. So that in my spreadsheet, that needs to be to the left of the information I want to look up and find. In this case, the rating. If your spreadsheet is not set up that way, you need to move the column that has the information that you're going to be able to type in the box. You want to move that to the left because that is a requirement. So if you keep those two rules in mind, you should be able to start using VLOOKUP successfully. So I hope that you found this video to be helpful. If you did, I'd appreciate it if you'd please click the like button below this video and please consider connecting with me on my social media websites like Facebook, Pinterest, and Twitter. And please do make sure you subscribe to my YouTube channel for more videos about technology for teachers and students and watch for another video from me at least every Monday.