Let's take a look at two practical examples
for 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 are 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, link is
below. Now I'm just gonna keep things simple here. So number two is the way you tell VLOOKUP
the column you want 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 in the left-hand side, remember that. The next argument is our number, what do we
want 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, customer
ID, company, and country. This information is sitting in a tab called
Master. 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 for 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. 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
is 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 mouse
click 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 put
a 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 to
Paul Hill. We have the right number. Now what happens if I wanted to get 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 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 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 things up? Well, let's just take a look, we want company
and country. So I'm just gonna highlight everything in
here. 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 on
to 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 Inkly here. Let's double check. Robert Speer is Inkly. 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 is
D32. 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's
Gary 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 this 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
or 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 dynamic
as 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 type 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 ID
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
these 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 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, link is in the description below. Okay, so that's how the VLOOKUP formula works. Let me know in the comments below if you're
using it in your files and also let me know if you're using helper cells for the column
index numbers. Thank you for watching and I'll see you in
the next video.
