While many people disagree, I feel the VLookup function is one of the top 5 functions in Excel. The Vlookup, or vertical lookup is an Excel function that will search a table for a piece of data and then return a corresponding cell based on your input. It can be very powerful but there are a few things to know in order for it to work to it’s potential. There’s also some common errors to avoid, which I will go over.
As always, the best way to learn is through examples, so I am going to give 2 examples to explain the Vlookup.
To download the sample Spreadsheet to follow along,Vlookup Tutorial
For this example, we have the following spreadsheet.
On the left side is the data we are trying to populate. It is a few students and we want to find their grade in the larger table on right. We could manually look through it. Instead we will use the VLookup function.
So go to cell B2 and insert a function and insert the Vlookup function.
The VLookup wizard will come up. We will need to fill it out as below:
Lookup Value – This is the cell we want to look for in the larger table. It is normally a reference.
Table array – This is the table we are looking in. The left most column MUST contain the value we are looking for. I use E:G as the reference. I like to use this so when we copy and paste the formula down, it won;t change the reference of the table we are looking in. If I used E1:G14, then when I would copy the formula down, in cell B2, the formula would become E2:G15 and keep increasing as you copy it down.
You could use $E$1:$G$14. Dollar signs ($) are anchors and that means the numbers won’t change when you copy it down. You also could use a named table, if you named it.
Col_Index Num – This is the number of column we are looking to pull in. You start with the first column in your Table Array and count over to the column you want. In this example. the table array is E:G. So if we want to pull the data in E, we would put 1. If we wanted the data in F, we would put 2. Since we want what is in column G, the grade, we put a 3.
Range Lookup – This tells Excel if we are looking for an exact match or not. Since we are looking up the student name, we want to find the exact match, so we would put FALSE.
So now after we press enter, we will see a 93 in cell B2. It just pulled in the grade for Tim in the large table. If we copy it down to C2 and D2, you will see the following results:
I color coded it so you can see exactly where the matches are located. Here is the formula we used if you don;t want to use the wizard:
One item to note: What Excel did in this example was look for the name Tim in the left most column of the table array. It starts at the top and works its way down until it finds the exact match. When it does, it counts columns. We put a 3 in the so it goes to the 3rd column and pulls in that field.
If you look at the above for the name Joe, there are 2 instances of it. Excel just finds the first instance and pulls in the data from the 3rd column. Then it ends, so it will never look at any other instance of the name.
This will work slightly different than the above example. It will still look for a match like the example above. However if it does not find one, it looks for the next closest match below the value.
We have the following example. We have some student with number grades on the left. We want to know their letter grade. The chart for letter grades is on the right. An A is from 90 to 100. And B is from 80 to 89 and so on.
We go to cell B2 and insert a function. Go to the wizard to see the following:
Lookup Value – This is what we want to look up, which is our number grades. For this first example, the letter grade is 88.
Table array – This is the table we are looking at. In this example it is from cell E1 to G6 or we can use E:G.
Column Index num – This is the column number we want to pull. We want the letter grade, which is the 3rd column.
Range Lookup – Here we want to use TRUE. If we use FALSE, it will look for an exact match. Since we won’t find an exact match most instances, we use TRUE. What Excel will do is look through the whole leftmost column of the table for an exact match. If it can’t find one, it will look for the next closest match which is lower than the number. Since the number is 88, the closest match lower than 88 is 80. So Excel will go to the row with 80 and go to the 3rd column which is a B.
** one thing to note, the numbers in the table array, column E, must be in ascending order.
If you don’t want to use the wizard, the formula would look like:
So the results would come up as below after you copy the formula down:
This error comes up when you are searching for something in the table and it is not there.
In this example, the table on the left has VLookup formulas in column B. There is a #N/A in B3 because it is looking up the name Tony in the main table array but there is no Tony in there. If you look in the table on the right in yellow, you won’t see a Tony, so it returns an #N/A.
This error occurs when we are looking to pull a column that is not in the table array we referenced. For example:
We are using this formula: =VLOOKUP(A2,E:G,5,FALSE)
A2 is the name of the student we want to look up. It is Tim
E:G is the table array we are looking at
5 is the column we are trying to pull. This is our error. We start counting at column E. E is 1. F is 2. G is 3. H is 4 and I is 5. However, the formula only referenced E:G. So since column 5 or I is outside of the table array, we get an error #REF!.
This error is the hard to spot. You might be looking for the number 123 in a table and 123 is in that table, however you still get an N/A error. This can be because the 123 might be text in one spot and a number in the other. You have to look at both items and make sure they match completely. There may be a little green arrow in the upper corner for a text. When you click that green button, there is a drop down to “Convert to number”. After you do this, it may work. If not, you have to keep looking at the differences of the two items.
The final error is the toughest. You may have the following:
We are looking for the name Tim in the large table array and it appears we found it. However there is still a #N/A error. Why?
Well a reason could be unwanted spaces in one of the names. If you look closely at them, the Tim on the left looks like this:
That looks fine. However the Tim on the right looks like this:
If you look closely, there is a space before the name Tim. You would need to delete that space and then this would work. If you have this error in many cells, you can you the function TRIM to fix all of them quickly.
So, this concludes my VLookup function tutorial. I really hope this helps you out. I feel Vlookup is a great function and I hope you get to use it as much as I have. Please send me feedback on this article or comment below. And as always, PLEASE SHARE WITH YOUR FRIENDS!
**If you are still interested in learning more about the VLookup, check out “The VLookup Ebook”. This book does a great job of explaining the Vlookup with multiple examples. Check it out here.