|
Today I’m going to cover one of the most important functions in Excel, and probably going to be one of your favorite functions as well. It is VLOOKUP.
What VLOOKUP allows you to do is that if you've got 2 tables and you got 1 common field in these 2 tables, you can pull data from one table into the other table. Meaning that you can look up this common field from table 1, find it in table 2 and return any other field from table 2.
Let’s say for example we have these 2 tables below (you can download the example file from here). One showing the city and the population of each city, and the other table is showing the city and its relative country. So the common field here is the city. Although they may not be listed in the same order, but that’s fine. We need to make sure there is a common field between them. So what we can do is look up each city from table 1 in table 2, and return the country field to table 1. You may think of VLOOKUP as your "find and return" soldier.
The V here stands for Vertical, because we are looking up values in a vertical table. It’s twin is the HLOOKUP in which you use when looking up in a horizontal table.
Now this is how VLOOKUP works. Go to the formula bar, type VLOOKUP and open bracket. The first argument here is the lookup value: This is the common field (in Table 1) that you are going to look up in the other table (Table 2). In this case it’s going to be the city London. The second argument is going to be the table array: Where are you going to lookup the work London? So I choose the whole Table 2 in columns I and J. The third argument is the column index number: This is how many columns across do you want to return data from; including the first column. So in Table 2, country is in the second column, so I just type 2. The final argument is where you choose either TRUE or FALSE: This tells Excel what you want it to do if it didn’t find the value you are looking for. Do you want it to return and error (FALSE) or do you want it to return the closest match (TRUE). When you select TRUE, if Excel didn’t find London in the lookup table, it will try to find the closest alphabetical match, which is probably Liverpool. But we don’t want that because it will be misleading and messing up the data for us. So we will choose FALSE. Actually, 99% of the time, I’ve used FALSE and I can barely remember when was the last time I chose TRUE. Note that Excel recognizes TRUE and FALSE as 1 and 0 respectively. So instead of typing FALSE, we can just type 0 and it works the same way. 
Now we drag this formula to the rest of the cells in the table. But something goes wrong. We see a lot of errors, which means the city was not found in the lookup table, or this is caused by something else.
Let’s investigate through the first probability. It seems that all the cities in Table 1 exists in Table 2.
So let’s go check the formula in one of the cells that is giving me an error. If we double click on one of them, we will see that the lookup range has been dragged with me, thus missing some of the cities. 
To fix this, we need to always make sure the lookup range is absolute (by choosing the range in the formula bar and hitting the F4 key) before dragging it.
Making the range absolute will show as a $ sign before the row and the column references. 
Now when we drag again from the first cell in the column (cell C2), the results will be displayed correctly.
Another way instead of making the range absolute is that we can choose the whole range of I and J columns instead of choosing exactly cell I1 to J21.
This is helpful in many ways:
Now let’s try to delete London from the lookup table and see what happens. 
I got an error instantly in Table 1 in the country field.
Now let’s try to update the formula VLOOKUP formula to have the last argument as TRUE instead of FALSE (I’ll just type in 1). 
So Excel have looked for a similar city to London in terms of the letters in the word London.
To know which city, let’s change the 3rd argument (column index number) in the VLOOKUP formula to 1, so that it returns the city instead of the country. So it returned Liverpool, which is the closest alphabetical match to London in the lookup table. 
But remember, we don’t want that. And I strongly advice to be very careful when choosing the approximate match because otherwise you can have flawed data and it will be hard to investigate the root cause.
A couple of important things to note about VLOOKUP:
Now go ahead and practice some VLOOKUPs. If you get stuck, feel free to post any questions here in the comments section. Happy Excelling! |