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]

Â