Vlookup Formula in Excel || vlookup in excel

 

Let's take a look at two practical examples of VLOOKUP. But first, let's take a look at some theory. Don't worry, it's not gonna be too boring. 

We have a sample data set, we have a column for name, age, and zip code. 

Now let's say we're creating a mini-report somewhere where we wanna look up a name and return their zip code. 

So it's kind of like we're filtering the data but in a dynamic way. 

Now the great thing about using VLOOKUP is that this data set can obviously be on a separate sheet and your final report is on its own. 

There are two things to take care of when it comes to the VLOOKUP formula that is really important. Number one is that whatever you're looking up has to be on the left-hand side of the table. 

Okay, so for example, let's say we're looking for Joe. Joe has to be on the left-hand side of the table and we're gonna return the zip code, which is on the right-hand side. 

What we can't do is go the other way, so we can't look up the zip code and return Joe. 

Now I know that some of you are thinking yes, you can because there are ways to tweak the formula to get that done. 

But there are actually better formulas that can do this, like the Index and Match formula, which I happen to have a video on, the link is below.

Now I'm just gonna keep things simple here. So number two is the way you tell VLOOKUPthe column you want to be returned. 

So how does VLOOKUP know that we wanna have the zip code and not age? You do that by giving it numbers. 

So the first column in our data set is number one, number two, number three. 

Okay, so let me just rewind and take you through the formula. So the first argument in VLOOKUP is the lookup value, in our case, it's Joe. Number two is where is it looking it up? 

It's looking it up in this range, so we have to give it this. Joe is on the left-hand side, remember that. 

The next argument is our number, what do we want to be returned? We want the zip code. What do we give it? Number three. 

Now, there is one last argument that's optional, but we're gonna need it here to get our formula to work, and we're gonna see that in a second.

So here I have a list of customers, customers, companies, and countries. This information is sitting in a tab calledMaster. 

Now what I wanna do is to create two reports, one has a list of customers and I wanna get their respective company and country back here. 

And number two is that I receive a bunch of customer codes and I wanna see do these exist in my master data. 

Before we use VLOOKUP to solve these, let's quickly practice it here. 

Let's get the customer ID for Kim West. VLOOKUP, the first argument is the lookup value, that's G5. 

The next argument is our table array. The table array has to include two things, it has to include our lookup value on the left-hand side and what we're looking up. 

So we're looking up customer ID, our lookup value is right here, so we basically need these two columns. 

And I'm gonna use Control + Shift + Down to highlight this whole area. 

The next argument is the column index number, that's the column we want to get back. In this case, it's number two. 

Now the last argument is optional, but it's quite important because we need to decide if we want an approximate match or an exact match. And because it's optional, a lot of people omit it from the argument. 

And by default, the value is true, which means that we're looking for an approximate match. So check this out. 

Does Kim West have this customer ID? Kim west is here. No, Kim West has 4991. 

And the reason our VLOOKUP is not working because our data set is not sorted, So basically the column that includes our lookup value is not sorted. 

Because check this out, if I just right mouseclick and I sort this, I get this number back, which is the correct number. Okay, so keep that in mind. 

If you're gonna omit that last argument, make sure that your data set is sorted. So I'm just gonna press Control + Z to go back. 

Now in this case, because my data set is not sorted, and if you're not sure if yours is gonna be sorted or not, make sure you puta false as the last argument. 

Now you can also put a zero instead of typing out false. And here we get the correct number. 

Let's just check this by changing this total Hill. We have the right number. Now what happens if I wanted to get the company back instead of customer ID? I can just change this to a three. 

It's not working. Why? It's because my table array needs to be expanded to include the Company column as well. 

Right, so even if in this case I don't care about Customer ID, I have to include it. 

I can't give VLOOKUP different ranges, I have to give it one range. And whatever comes in between your lookup value and the result you want, you still need to be included in your table array argument. 

Now let's get to completing these reports. And the reason I have many names here is that we need to take care of our fixing. 

So let's start off with VLOOKUP. And at first, let's just not fix anything and see what happens. Where's the lookup value? It's this one. 

Where are we looking at things up? Well, let's just take a look, we want company and country. 

So I'm just gonna highlight everything inhere. So I can include the header or I can omit the header, in this case, doesn't really matter. 

Now I'm not gonna fix it, I'm gonna move onto the next argument is the index for Company. Which is which one? It's number three. 

And I do want an exact match, so actually instead of typing out false, let's type zero, close bracket, and press Enter. 

So I get Inky here. Let's double-check. Robert Speer is Inky. Okay, so now let's just push this down. I'm gonna double click here, and it works for this one. There's a problem here. 

I didn't fix this range and notice what happens to that, it's A4 to D32. It starts at A4 and we end here, which isD32. But when I pull this down, that entire range is coming down, so it's now A6 to D34. 

And take a look at who I am looking up, it'sGary Miller, but I'm only looking from A6. 

So Gary Miller must be somewhere on top, which is right here. My lookup range has shifted down to here. It's not finding Gary Miller, so it's giving an error. 

So the fixing is really important, make sure you always fix your range. And you can do this by highlighting these and clicking on F4. 

Okay, so now let's push this down and we have the right company for Gary Miller as well. 

So now what I could do to get the country is I could rewrite this VLOOKUP here and just adjust it. Or just make sure I do the fixing of this one correctly, and then adjust this number. 

Or what a lot of people do is that they work with index numbers inside helper cells. So instead of them typing out these three here, they actually put it somewhere on their report. 

So let's say three here and four here. This way they can just make one formula, do their fixing correctly, and then pull it across and down. 

So how would I update this formula and use these index numbers here? Well, first off is this C6. 

When I pull this here, I don't want the column to change, right? I want C to stay fixed here, but when I pull this down, I want the row to be seven, and then eight, and nine.

 So this means I need to fix the column but leave the row variable. You can directly put in a dollar sign here you can play around with F4 because it's a toggle. 

So if I click it again, and then again, the dollar sign changes position. 

Now for this one, I wanna make this one dynamics well, so I'm gonna click on this cell. How do I fix this one? 

Well, when I pull this over I actually want the column to change, but I don't want the row to change when I'm pulling this down. So now I'm gonna press F4 again and fix it in this way. 

Okay, so I have a separate video on relative and absolute cell referencing, which is what this is called. 

So if you're not sure about this, check out that video. 

Now let's see if it works. I'm gonna pull this across, pull it down. Let's double-check, for Wolfgang here we should have Arcade and Germany.

 So let's go and see, where we see Wolfgang, we have Arcade and Germany. 

What a lot of people do is they hide these or they group these rows together, or you can just make them very light gray or make them white so that they're not fully visible. 

There is a way that you can avoid using these helper cells and you could use indexes in your formula directly, for example, using the columns function. 

Okay, but that's just gonna get more advanced, and especially if you're sharing these types of workbooks with other people in your team and they're not so advanced in Excel, it's just better to keep things very simple. 

So if they wanna make an adjustment or change something, they can easily follow your formula. 

Okay, so let's move on to Report Two. Are these customers included in master data? Okay, so I get a bunch of code names, I wanna know do these exist or not. So I'm gonna start with VLOOKUP. 

My lookup value is this one. In this case, do I need to fix this? No, right, because I'm just gonna pull it down, there's nothing to pull across. 

Table array, what is it now? Well, I only wanna know if this customer exists, so it's actually enough if I just highlight the values in this column. 

I have to fix it, then the next argument is what should it return? I'm gonna put a one. 

So basically if it does find it, it returns its own value. And I'm also gonna go with false because my data might not be sorted. 

So let's push this down and I can see that there exist and this one doesn't exist. And if you don't wanna see this hashtag N/A, you can wrap this up inside an If Error formula. 

So if the VLOOKUP formula results in an error, what should it do? It should say add to master. 

Okay, so you can type in a text, you can write a formula, whatever you want it to do if it doesn't find it in the master data. 

Now when I push this down, I can see this one I have to add to master. Some of you may ask what if you wanted to get the customer name instead of the customer code? 

Instead of tweaking the VLOOKUP formula with formulas like Choose and we can get more complex, use the Index and Match formula. 

which is super simple to learn once you get the hang of it, a link is in the description below. 

Okay, so that's how the VLOOKUP formula works.