How to Use VLOOKUP in Excel (free file included)

How to Use VLOOKUP in Excel (free file included) Here's the thing, if you use Excel, you need to know VLOOKUP. It's the most used Excel function there is. It helps you automate your data analysis, obviously, help you save a ton of time and avoid errors, which is all we want, right? It's also the OG lookup function in Excel because it works in all Excel versions. So here's the deal, by the end of this video, you are going to know what VLOOKUP is, how it works, how you can look up a value from any another sheet,.

How you can look for an exact match or the closest match, and if you run into an error, you are going to know how to solve it. Okay, so this is going to be your ultimate VLOOKUP guide, and you need to pay close attention because I am going to have a test for you at the end to make sure you understood everything. So now let's start with our first example. Here we're going to take a look at the most common use of VLOOKUP, which is to look up related information from another place.

And bring it over here. So in this example, we have a list of customer names, and we want to find each customer's profession. That information is available somewhere else, that happens to be right here. So we have customer names, phone number, and profession. Now, this list happens to be short, but it could be, like, in reality, it could be thousands of rows of data. You don't want to do this manually, right? You don't want to look each person up here, find the information.

That you want, copy it or type it over here. You want it automated, and that's what VLOOKUP helps us do. So the first thing we need to do is to go to the cell that we want our answer in, then start typing in an equal sign and just start writing VLOOKUP. As you start to write it in, you're going to see it pop up here. Use Tab to accept it, and it also opens a bracket for you. Now inside here, we can see what this formula needs to be able to work properly. So the first thing that it needs,.

Or the first argument as we call it, that it needs is the lookup value. So what are we looking up? We're looking up Sid in this case. So we just have to select that cell. Next, we move on by using the argument separator in Excel. So in my case, I'm using a comma; you might need to use a semicolon. That's just going to depend on your regional settings, on your locale settings. So the next thing that Excel needs is a table array for VLOOKUP. This basically means the range it.

Should look it up in. Now that range must include your lookup value, and it must include the answer that you want. So the column that has the answer, you can't select individual ranges with VLOOKUP, you have to select an entire range, which in this case is this range right here. There is one rule that you have to keep in mind: the range that contains your lookup value has to be on the left-hand side of your result column. This means that name here in this range has to be on the.

Left-hand side of profession, and it is. That's important for VLOOKUP. Then we move on to column index number. This is the column number that we want returned. So Excel starts counting from one, so this is the first column, second column, third column. What would we put here? Number three, right? Because we want the third column returned. That last argument here is in square brackets, and in Excel, anything in square brackets means that that argument is optional. So here's the place.

Where a lot of people make mistakes, they ignore that last optional argument. Why? Well, because the formula works if I just press Enter. But is it correct? Sid is not head of procrastination, Sid is keyboard manager. If you're looking for an exact match, that optional argument isn't optional for you, you have to go with FALSE, which means you're looking for an exact match. Now when I press Enter, it works correctly. Just something to keep in mind if you are sharing workbooks with.

Colleagues and you don't see them using FALSE in there, instead you see them using zero, that's not a mistake, that's also correct because zero translates to a FALSE in Excel. You either need to have a zero or FALSE for this exact match to work correctly. If I drag this down, does it work? It kind of works, but it doesn't fully work. Why? Well, if I go into edit mode, notice that my range, that area is shifting down. Moltisanti is no longer in that range..

So if you're planning to copy down your formula, you have to fix that table array range. And to do that, just select it and use the F4 key. Now when I drag this down or just double-click to send this down, this works fine, my range isn't shifted. That's how you use VLOOKUP for an exact match. Now let's take a look at looking up value from another sheet. So here I have the list of names and I want to look up each person's phone number. This time, that information is available on another sheet.

Called Data Numbers. So I have the person's name, phone number, and profession. Now remember, for VLOOKUP to work correctly, the lookup column has to be on the left-hand side to the result column, so to what I want returned. That is the case here. This means that my VLOOKUP is going to work without problems. All I need to do is start typing VLOOKUP, lookup value is this one right here, my table array is my range on the other sheet. So all I'm going to do is just click to go to the other.

Sheet, we can see the sheet name popping up in the formula bar, and select the range that I want. I don't need to include profession because I'm only interested in phone number, so I just need my name column and the phone number column. I'm planning to pull down my formulas, so I'm going to press F4 to fix this reference. Next, column index number, what number is that? Number two, right? Name is number one, phone number is number two, that's what I want returned. I want to grab each.

Posts Related:

Person's phone number, so I put a two here. The last argument here is not optional, if you want

Each person's score, and we want to translate that score into a grade. But in our lookup value, we don't have each possible number in the grade, instead we're dealing with ranges. So anyone who has between 0 to 54 is going to get an E, anyone who's between 80 to 89 is going to get a B. For Excel, the way we provided the score and the grades is like this, we just specify the lower bound value. So here's a zero, here's 55, 70, 80, 90, so this is how Excel understands. This part is.

Just easier for us to understand what's happening. All we need to do then is start off with VLOOKUP, the value we're looking up is the score. Where are we looking it up? In here. Next, what do we want returned? We want to return the grade, that's the second column. So we're going to put a two here. Now, you can happily ignore that last optional argument because default Excel behavior is to look for an approximate match. So when I press Enter, I get E. 26 gives me an E. Now can I just pull.

This down? No, I need to fix this. I'm going to use F4, and then let's just double-click to send this down. So anyone who has 75 is going to get a C, and that is correct. Now, this doesn't mean that you can't put in a TRUE. In fact, it makes it more clear that you are doing an approximate match. Just one thing to keep in mind is that when you're using VLOOKUP with an approximate match, make sure that your scores, that your values that you're looking up are sorted in ascending order..

Now let's take a look at some common errors you might run into when you use VLOOKUP and how you can correct these. It's also a good way to test if you paid close attention during the first part of our video. I have customer IDs here, I've written a VLOOKUP formula to return customer names based on this range, but it's not working. US4556 is returning Silvrr, but it should return Blend. Why isn't it working? You probably guessed the answer, right? I've forgotten to add the FALSE in the end.

For the exact match. You can also type in zero, both is going to result in the correct customer name, right? Blend for this US company ID. I want to show you something interesting. I just press Ctrl + Z to go back, right? So here I'm returning wrong results for all of these, actually, but take a look at this. If I leave the formula as is, okay, so approximate match, not exact match, last argument is gone, is missing. If I go to my data range here, I right-mouse click, Sort, A to Z,.

Everything works properly. I get the right results. Why? Because for approximate match to work properly, your lookup range has to be sorted in ascending order. But I don't recommend that you do this because someone could come along and just insert another company ID in there and messes up all the sorting or if an ID doesn't exist. So let's say I have DE8364 and this doesn't exist in my lookup area here, I still return the closest match, and that's not something you want, at.

Least not in these types of cases, right? So I'm going to press Ctrl + Z a few times to go back, okay? So what we want to do is add FALSE or zero as the last argument, and when I send this down, it works, this one doesn't work. Why? Well, okay, I don't expect you to solve this because I did add a sneaky error here. There is a space right after this name, that's why it's not working. For it to work, I just have to remove the space, press Enter, and it works. But if I have a lot of data.

And some of them might have a space, some of them not, I want to take care of that in the formula. I could wrap this part, this reference, the lookup value reference inside the TRIM function. The TRIM function is going to trim any spaces at the beginning or at the end of the text. So when I send this down, everything works like a charm. Now, let's take a look at another common error you might run into with VLOOKUP, and that's right here. We want to look up the customer ID from here.

And return the customer name. I've written the function to look up B4 in this range here. I want

I get company ID back because I have to update the column. I won't return it to two now and send this down, everything works correctly. So you have to keep this in mind when you're using the lookup. If you want a more flexible function and you have a later version of Excel, so you have Excel for Office 365, you can use the XLOOKUP function because that function doesn't have this restriction. Now, another error you might come across is this type of error. So.

If a value doesn't exist, you're going to get an error, and that's good to know because you can go ahead and correct this, or if you want to return something else in case you have an error, you can use the IFNA function, for example. So if your function is returning #N/A, what do you want returned? You could put it in quotations. We can say "Not found". Close the bracket, press Enter, and send this down. You end up with "Not found" whenever something is missing. You can also use.

The IFERROR function as well and send this down, and we get "Not found" for this one. So how many of these did you figure out on your own? Let me know in the comments. Okay, so that's how VLOOKUP works. I hope you're super confident, and if you run into errors, you know how you can fix it. Now, if you have newer versions of Excel, if you have Excel for Office 365, I suggest you use the new lookup function that Microsoft introduced, which is called XLOOKUP. It's a lot easier to use and.

DISCLAIMER: In this description contains affiliate links, which means that if you click on one of the product links, I'll receive a small commission. This helps support the channel and allows us to continuetomake videos like this. All Content Responsibility lies with the Channel Producer. For Download, see The Author's channel. The content of this Post was transcribed from the Channel: https://www.youtube.com/watch?v=TDphx23AtqM

How to Use VLOOKUP in Excel (free file included)

Person's phone number, so I put a two here. The last argument here is not optional, if you want

And return the customer name. I've written the function to look up B4 in this range here. I want

Contact Form