The VLookup is the most useful and powerful function in Excel. I don't say that lightly. I've been working in Excel for nearly 20 years and have not used any other function as much or with as much utility and success.This tutorial is going to get you setup with the basics of building a VLookup. In a couple future posts I'm going to also show you how to use VLookup to identify a value exists in another sheet (ex. returned orders) and how to combine VLookup with the Match formula to make bullet proof formulas.
Full Transcript
Hey, everyone. Ben with Bensullins.com. Today, I'm excited to share with you an Excel tip on the VLookup function. Now, the VLookup is the most useful function in Excel. I don't say that lightly, I've been using Excel for twenty years and I've not found more utility or more success with any other formula or function than the VLookup. Let's dive in. (Screen recording) Okay, so here I have my sample superstore dataset, and what I want to do is use the VLookup function to find the manager for each region. You can see I have three tabs on the bottom. One has a list of all my orders, the next one has a list of returns, we use that in a minute, and then this one has the people in it. So the way the VLookup works is you need to find in the leftmost column whatever the field is that you're going to match with. So what we need to do here since we're gonna match on
Okay, so here I have my sample superstore dataset, and what I want to do is use the VLookup function to find the manager for each region. You can see I have three tabs on the bottom. One has a list of all my orders, the next one has a list of returns, we use that in a minute, and then this one has the people in it. So the way the VLookup works is you need to find in the leftmost column whatever the field is that you're going to match with. So what we need to do here since we're gonna match on region is we need to actually copy this field here over to the right side because VLookup works by finding the matching entry in the first column, and then returning a corresponding column from the right. So we have to have whatever value we want to return on the right side of our match. So if I go back to orders, I happen to have a field here called Region, and what I wanna do is just jump to the end here and I'm gonna type manager. I'm gonna go down here and type VLookup, I'll hit tab and it'll give me the prompt there and you can see the format: lookup value, table array, where we gonna look it up, the column number that we're going to return, and then if it's a range lookup or not. I'll cover that in a later tutorial. Right now is just get the basics down. So the lookup value is going to be M2. This is the region for this order. Then, I'm gonna hit comma, and notice that the helper here tells me now that it's a table array that it wants. And what I want to do is find the data that corresponds to this, so I click on my other tab, People, and then I can highlight the data that corresponds to it. Now if I selected the data like this, and there were more rows where I would add stuff later, I would miss that because I've selected specifically this range. So instead, what most people do is you highlight the entire thing, hold SHIFT, and then click on the other corresponding columns. You could do this all the way over, even if you have a giant data set, it doesn't matter. This way you're getting everything in column B and everything in column C. All the rows. And once we've found our range, we're gonna hit comma again and it's gonna ask us which column do you want to return? And it's using an index number so it's looking for the number of the column returning. In this case, column B is number one, because that is the column that is in the left spot of our table we're giving it, that people B, colon C. So here we want to say too, you want to return the second field in our dataset, in our table array that we're doing our lookup in. From there I hit comma. Now a range lookup is if I had say, a salary band or a date and I wanted to see the closest entry without going over, but not the exact entry, I could put a one here. Instead I'm gonna put a zero. Meaning I want an exact look up. I don't want to find something close to sell, I want to find South specifically. So I hit Enter and it brings back the name, the manager, the person that's listed for the south in the People field. Let that sink in for a minute. I was able to just go over to a different sheet programmatically and return a value. And you think okay Ben, that's fine, that's easy, I could've just copied that. Well, let's take a look. We have close to 10,000 orders here. I don't think you want to be doing that 10,000 times, so instead what you can do is you can highlight all the data here, and then you can hit Command D or Control D on your Windows machine, and it will duplicate this formula all the way down. that's one keyboard shortcut I like to use. I'll have a whole session on keyboard shortcuts later. But right now what you can see is that it actually copied that formula all the way down my dataset. How awesome is that? So now it just saved me a ton of time by automating the work I'd have to do by going and looking up the manager for this region here. So we can do it that way, and there's lots more to come.
I hope you enjoyed this Excel tip on VLookup basics. Stay tuned for more Excel tips including two more on advanced ways of using VLookups.
1 Comment