Oct 31, 2014

A VLOOKUP in Microsoft Excel looks up and finds a piece of information in a list, a list like the one below:

The V in VLOOKUP stands for vertical because you are looking vertically down a list. (Yes, there is a function called HLOOKUP, but isn’t nearly as popular because most lists go row by row, rather than column by column.) So, let’s imagine I ask you to find out ‘Item 16’s cost. You’d look down the list until you find the right row then look across to the right along the row you found and look in the cost column and then you know the answer ($47.22). That’s what a VLOOKUP does.

Let’s get a VLOOKUP to do what we just did manually. Let’s type 16 in cell B2. We’ll use this cell in the formula and then we can change B2 and watch the result of the VLOOKUP change. Then I click in C2 and start typing: =VLOOKUP( Every formula starts with an = (equals sign), then I type the name of the function, notice how it gives a little box with a description of VLOOKUP to help us, then I type an ( (open bracket). Excel then gives us a very useful piece of helper text, it shows the pieces we need to put inside the brackets and the order they go in. Once you’ve done any function a number of times, this is something you can use as a quick reference.

I’ll talk us through what each piece means: The first piece is the lookup_value, that’s the piece of information we already know, so that we can find the correct row in the list. We know the Item number of what we want to find i.e. 16. However, we typed 16 in cell B2 so we put B2 as the lookup_value. Type a comma (,) to separate the pieces inside the brackets. The next piece is the table_array; sounds fancy – it’s not. It’s just the list of information. Our list is B4 to F24 so we put B4:F24 in as the table_array. Technically, we only need from the left hand column across to the column we want information back from B to D, it won’t hurt and next time we might want information from column E or F. By the way, it only looks for the 16 in the left column of the list, even if there is a 16 in one of the other columns, the VLOOKUP won’t find it. Type a comma (,) to separate the pieces inside the brackets. The third piece is the col_index_num, or reading it in full the column index number. What column to we want back? Column D, but we can’t put a D because it says column index number not column index letter. So counting across from the first column of the table (not the sheet) we find it is column 3 so that’s what we put in 3. I’m going to ignore the last piece for now, it’s optional. I’ll come back to it in a second.

We close the bracket and press Enter. The VLOOKUP should be giving the answer 47.22, just like we found out manually. Congratulations! Test the VLOOKUP by trying different Item numbers and it works. When I try number 25 (even though my list only has 20 rows), it still gives an answer, but it gives the answer of item 20’s cost. If we choose an item number that isn’t in the list, it gives the nearest item rounded down. Even if I try 16.8 it still gives 16’s cost (not 17’s cost). For this type of list, this isn’t good. The first time I ever did a VLOOKUP, I was expecting the VLOOKUP to give me an error message so I would know I was looking for an item that wasn’t there. This is where the last piece in the brackets comes in, click on C2 and then click in the formula bar between the bracket and the 3 and type a comma (,):

The last piece is called range_lookup, again sounds fancy but isn’t. It’s really saying “do you want the nearest one rounded down (an approximate match) or do you want exact matches only?” If you want exact matches only, and we do, we put FALSE. If you leave out range_lookup, it assumes you put a TRUE. That’s why it was giving us approximate matches before. Now, if we look for an item number that isn’t in the list it gives the error message #NA, which is good because now we know that item isn’t in our list. It still works fine for the other items, like 16, so we get exactly what we want. Next time I’ll show you how to use a VLOOKUP to bring two lists of information together, as well as how to quickly compare two lists to find out what is in one list but not in the other. A classic use of a VLOOKUP!