The "VLOOKUP" function is one of the most used look up functions in Excel.
It allows you to look up values from another place, another sheet, or another data table.
The great thing about it is it works in all versions of Excel.
Let's take a look at our example.
We have a list of names, the yearly salaries.
Now we need to get the department and the bonus percentage for each person.
That information was sent to us.
Let's assume by our HR department and it's in the tab master data.
So here we have a lot more names, the division,
each person works in, the department. And on this side we have a list of salaries and the bonus percentage
associated with each level of salary.
So based on this information we need to figure this out.
Now of course on smaller data sets you can do all of this manually right.
You can take a look at Gary Miller, go here, look for Gary Miller,
Copy and paste his department or division there,
Take a look at which level salary he falls into, copy and paste this value in.
But the moment you have a lot of data, your master data is huge
you need to do this in a dynamic way. That's when you can use the "VLOOKUP" function.
Let's do an example with department and I'm going to explain the function here.
So we're going to start off with "VLOOKUP".
It needs a few arguments.
First off is the lookup value.
Which value are we looking up?
Well, we're looking up this name.
Then the next argument is where are we looking this up?
Well, we're looking it up in this table.
But notice something special about this dataset and what I'm highlighting. The name of the person that
I'm looking up is in the left hand side. And division and department are on the right hand side.
I can't highlight this because it's never gonna find Gary Miller in here. It's going to look in the first
column on the left hand side for my lookup value.
That's not the right column.
I need this.
I want department back and division is in the way.
It doesn't matter.
You have to highlight one range of cells.
Even if I have five or six different columns between the name and the department, I have to highlight
them as well.
So remember, important part is that the lookup value is on the left hand side of my table array.
Next is the column index number.
That's the column I want returned.
In this example it's department and I have to type a three in here.
The reason is it's the third column in the range that I highlighted. Last argument is very important.
That's if you want an approximate match or an exact match.
It's an optional argument so people tend to forget about this but it's actually important.
Otherwise you might get the wrong answers.
In this case I want an exact match.
I wanted to look for exactly Gary Miller and give me back the department.
So I have to go with false.
We can also put a zero
instead of putting in false.
Now when I press enter, I get finance.
Gary Miller works in the finance department.
Let's just double check.
He does. Now before I copy this formula down.
There is some cell referencing that I need to take care of. This one A5,
This I need to be dynamic so I don't need to fix the column or the row here. For this data set
that's something I don't want to shift at all. Press F4 and now press enter.
Let's just send this down.
We get our departments. For the last one,
I have an error.
And that's the #N/A error.
A very common error when you work with "VLOOKUP". The general cause of this is the value you're looking
up doesn't exist in your dataset.
So a way around this is to use the "IFERROR" function.
But there is another error function that came since Excel 2016.
If you have Excel 2016 and above, you can use "IFNA". The value is actually our VLOOKUP.
And if it's an error, we want nothing returned. Close bracket and push this down.
If you have a version of Excel that's before Excel 2016 use the IFERROR function instead.
Okay.
Now what happens if you leave out that last argument.
Well if you do that there's one thing you need to make sure of.
If the column that has your look of value is sorted you can leave out the last argument for the VLOOKUP.
But if you're not sure whether it's sorted or not or if the sorting could change then make sure you
put in the false for that last argument.
Now let's take a look at doing the VLOOKUP for the salary.
So here I have exact salaries of each person but I want to take a look at this table that shows me the
bonus scheme and I want to allocate the bonus percentage for each person based on the scheme here.
So now I can't use an exact match.
I have to use the approximate match. But there is one thing I need to make sure of when ever I use approximate
matches in the VLOOKUP function. And that's my look up column has to be sorted and this is sorted.
So now I can comfortably go back and use the VLOOKUP function. First argument is the look up value.
What's that going to be in this case?
It's my actual salary.
The table array is sitting in master data and it's these two, right?
Make sure the look up value is the first column that's included in the table array. And it is. The column
index number.
What do I want returned?
I want the bonus returned.
What's the index number for this? It's the second column in the range that I selected.
I'm gonna put a two and for the last argument notice that it's in square brackets.
This means it's an optional argument in Excel.
True is the default option which is an approximate match.
So I'm going to leave it out, close bracket press enter. And before I push this down, what do I need to make sure?
That I've done the fixing for the cell reference correctly. For this one,
I need to fix.
So let's push this down and I get the bonus percentage for each person.
Let's just double check.
Robert Marquez earns one hundred thirty four thousand, so he must have the highest bonus percentage.
Which is correct, right? It's above 100,000.
Let's take a look at someone who's earning a 10 percent scheme.
That means they must be earning between 60,000 to a 100,000.
So let's go here.
That's Robert Spear.
Gary Miller and Kathrin Nehmeh.
So for Kathrin Nehmeh, we couldn't find her department but we could get the bonus because the driver
of that lookup table is the yearly salary
and we have that information.
Okay.
So that's how you can use the VLOOKUP function.
Just remember that it's important to understand how to use the last argument for it.
Anytime you're looking for an exact match
don't forget to put false for that argument.