0:00

[MUSIC]

The VLookup is one of the core functions in Excel.

And one of the most frequently used.

The VLookup allows you to lookup a value in a data table and

pull a specific attribute corresponding to that value, to a new table.

For example, let's say I have a data set with a name on it.

Now imagine I also have a data set that started with the same name,

followed by a series of attributes related to that name.

VLookup is going to allow me to lookup the name in the second data set.

And then select which attribute or

attributes I'm interested in pulling into my new data set.

VLookup's work fast in structured data sets.

And your lookups range always needs to be all the way to the left of the table.

The V in VLookups stands for vertical.

There's a separate formula called HLookup which stand for horizontal lookup.

Which allows you to lookup the data horizontally.

Where the vertical lookup, the VLookup, looks vertically across the selection.

Generally, we see VLookups more frequently than HLookups

due to the way data is normally structured.

However, HLookup is a good tool to have in your pocket.

The syntax for VLookup is fairly straightforward.

It starts with a lookup value.

Essentially, what am I trying to lookup?

The next input is the table array.

Where am I trying to lookup this information.

And next, is the column index number.

How many columns from the matching value do I want to look over and

bring into my data set?

The last piece in brackets is an optional field called the range_lookup.

This is asking whether I want an exact match, or a partial match.

Most of the time we want an exact match.

But it is a leading practice to specify true or

false, something we need to be sensitive to as we go forward.

Some other things to keep in mind are the VLookup is always going to take the first

match that it finds in the data set.

It stops looking once it finds a corresponding match,

even though there may multiple matches further down.

Second, if Excel cannot find a match, it is going to come back with an error.

We will learn how to deal with both of these problems

as we become more familiar and we practice using VLookup.

So, let's now shift actually playing with VLookup with an Excel.

Why don't we start with basic VLookup problem.

2:41

First, let's get familiar with our data set.

And the question we are being asked to answer.

Over here we have a very basic table, starting with distributor ID,

the distributor country, and the distributor name.

Down here we have another table with just the distributor ID.

The first question asks us to match the distributor's name

to the distributor's ID.

This is a perfect use case for a VLookup.

So why don't we get started.

3:15

And as discussed prior,

we are going to start writing our formula plucketing the equals T.

Now we are going to start typing the name of the formula which is VLookup.

I am going to hit the tab key which will fill out the rest of the formula.

You will notice that within the VLookup formula, the lookup value is folded.

This allows me to know which section of the formula I'm working in.

The view with the value I want is the distributor ID.

Since I'm trying to pull in corresponding information with the distributor ID

being the common key.

I'm going to select 23265, the first distributor ID, and hit comma.

Next, I'm going to select where I want Excel to lookup this information.

I'm going to select my entire data table from the top left to the bottom right.

4:18

If we are copying and

pasting this formula we'd like to keep the data table consistent.

So I'm going to hit the F4 key to get the $ sign next to both the row reference and

the column reference.

Next, I'm going to click comma, and

it's asking me to insert what column index number I am looking for.

In other words,

how many columns over is the data that I want to pull into my table?

Starting with the data that was being lookedup.

The distributor ID will be in column one.

The country in column two and the name in column three.

4:59

Now, I'm going to hit comma to move to the next piece of this syntax.

Which is range_lookup.

The range_lookup is an approximate match or an exact match.

The best practice is generally doing exact match.

So I'm going to type false and then close the formula.

You will see that the answer is Uriel Benton after I hit Enter.

Note, this matches a name for the distributor ID in B21.

Since we properly locked and anchored the formula, we can copy and

paste down the formula to get the matching distributor to 23315.

Let's look to see what happens when we copy and paste.

We will see Anika Tillman appeared for

distributors ID 23315.

[MUSIC]