All language subtitles for 1. Looking Up Information using VLOOKUP (Exact Match)

In this lesson, we're going to learn how to look up information using some of the best loved functions

in Excel and those would be look up functions and probably the most well known lookup function is V

look up and vena copies.

One of those functions that you'll always see at the top of the list when it comes to most useful functions

or functions that everybody should know.

And that's because you can use fee lookup in so many different scenarios.

Now, when it comes to using V lookup, there are two different ways that you can use it.

You can do an exact match or you can do an approximate match.

And in this case, we're going to start out by taking a look at the more common of those two, which

is V lookup exact match.

Now, before we get on to how you construct a V lookup, let's first make sure that we all understand

what exactly a V lookup is and how it can be useful.

So I've just got a little example of a V look up on this page, so I have a small data table which contains

some order numbers.

The items that these order numbers relate to and then the price of those items.

And what I've done over here is I've constructed a little data validation dropdown list for all of the

different order numbers.

And what I can do here is select an order number from this list and it's going to return the item and

the price.

And if I click on any of these cells just here, notice in the formula bar, I'm actually using a V.

Look up now with this formula was actually doing is it's basically saying look up, whatever value we

have in Cell G three, look it up in this table over here and then return the information from column

number two and then the Veilleux Cup underneath is basically doing exactly the same thing, but it's

saying instead, return the information in column number three.

So that is how V lookup works.

Now, a couple of really important points.

When you're working with V, look up what you're using as your look up value.

So for us in this example, that is the value in Cell G three, it has to exist in the table of data

because without the lookup value, exon doesn't know which of these results it needs to return.

So that is basically how a V lookup works.

So now let's look at how we construct it using a different example.

Now, if we jump across to the next worksheet, you can see that I have on here just a few different

part numbers.

And currently, the description and the unit price columns are blank.

And then on the third worksheet, I have eight parts catalog, and this contains the part numbers in

Column A, the descriptions in Column B and the price and column C.

So basically, what I want to do here is I want to use the part number as the lookup value because this

is the column that is common between these two tables.

I want to look up the part number in the parts catalog and then I want to return either the description

or the price, depending on which one I'm doing.

So let's construct our V look up.

Now, the first thing that we want to return here is the description.

For part, number one nine two three two.

So I'm going to type in equals v.

Look up.

Let's take a look at our arguments.

The first argument here is the look up value.

So that's the piece of information that is common between the two tables.

So for us, the part number is I look up value.

We then need to specify where we're looking up this part number.

So what is the table array?

It might be that you have your table in the same spreadsheet, and that is fine.

You can just select it.

If I jump across two parts catalog, I can select this entire table.

And that is my table, and right now I'm going to go to the formula bar to continue my editing and notice

what I have in that formula bar now because my table is on a different worksheet, it's put in the worksheet

name parts catalog and then the cell range.

I now need to specify the column index number, and that is basically the column of information that

we want to return.

Now we're looking up the description first of all, and this is a really important point about V. Look

up when it comes to the column index number V lookup numbers, columns in the table from left to right.

So part number would be column one, description column two and price column three.

So if we want to return the description, we're returning column number two and our final argument,

which is an optional argument because it's in brackets.

Is it true or false argument?

And what we select here determines if we're doing an approximate match or an exact match.

Now we're going to do an exact match because we exactly want to match the part number in the table so

we can have a false argument on the end here.

Or alternatively, we could just put zero.

That would also do an exact match or because this is an optional argument.

I don't even need to have that on the end because the default behavior for V lookup is to do an exact

match.

So I'm going to leave it off.

Let's hit enter and it brings back that description information.

Now I'm going to widen out this column a little bit, and I think I'm going to wrap this text around

just so it doesn't take up so much room.

Let's do the same thing, but let's do it for unit price instead.

So we're basically going to construct the V look up in exactly the same way.

It's just the column that we're returning that is different.

So this time we want to return column number three.

So equals v look up, look up.

Value is still the part number.

Our table array is our parts catalog, and I'm going to show you a simpler way of doing this in a moment.

Let's select the entire table and go up to continue editing in the formula bar.

The column index number where we want to return the information from column three and we're exactly

matching that part number so we can have false zero or nothing on the end that hit enter.

And there we get that unit price.

Now you might want to do a little bit of messing around with the formatting of this cell, but that

is basically how Vila Culp works.

Now, if I was to try and copy these formulas down, they're actually not going to work correctly because

I haven't locked the cell range, so I know it looks like they're working fine.

It's actually returning the wrong results down here from the table because I didn't lock the cell references.

So as I dragged down, this entire table is moving down as well.

So a way that I like to do this, I find this a lot easier is if I'm doing a V lookup in general, I

will make my table of data a named range.

So let me just very quickly do these again and show you how that works.

So I'm going to go to my parts catalog, control a to select everything, and in the name box, I'm

going to call this parts under skull catalog.

Now, by naming this range, it makes your V look up a lot easier to construct.

So let's go to V look up.

I look up.

Value is the part number, a table array.

Well, this time I don't need to jump across to the other worksheet and select that range.

I can simply press the f three key on my keyboard, and it's going to bring up all the named ranges

that I have in this workbook.

There is mine parts catalog.

Let's select it.

And the cool thing about this is that when you create a named range, it's basically very similar to

locking the cells so they're never going to move.

Column index number is two.

And we're doing an exact match.

Close the bracket control center and then I can double click to copy down and my results are correct.

Let's just do this again for the unit price, look up value.

Here's part number F three to pull up my name ranges.

Let's select the parts catalog the column we're pulling back.

This time is column number three, and we want to do a false argument on the end or a zero or absolutely

nothing.

It's all good.

Double click to copy down.

Now, one thing to note here is take a look at this part No, I have an Arar's in these cells.

Now I'm going to hazard a guess that I've got these errors because this part number doesn't exist in

the catalog.

So if I want to do a quick check, I can jump across to my parts catalog.

I'm going to do a control f for find, and I'm going to try and find that part number one two three

four five.

Fine, next.

It can't find it.

So that is why I'm guessing that an error.

Now, as we saw in the previous lesson, when we were talking about error checking, I might want to

tidy these errors up a little bit by adding in some error handling.

So let's click on that first formula up in the formula bar.

This isn't an error, so I'm going to use the F and a function.

The value is going to be generated by the V look up, and I'm going to make this a bit more meaningful

by just adding onto the end here, parts not found.

So anyone looking at this spreadsheet is going to know that that is the reason.

What we don't have a description and unit price.

Let's do the same for this one up to the formula bar, if any, click at the end.

And this time I'm going to say price not found.

Close the bracket control enter and then I can double click to copy down.

And now this table doesn't look like it includes errors.

We've added some meaning.

So that is how you do a v lookup using that exact match option, that false argument on the end.

In the next lesson, I'm going to show you another example, but this time we're going to be doing an

approximate match using the true argument on the end.