VLOOKUP: Data Retrieval in Excel

VLOOKUP. VLOOKUP is a very popular function in Excel. It allows you to look up information in a table that could be on the same sheet, on another sheet, or even in another workbook.

VLOOKUP, in short, is a vertical lookup. There are four arguments for the VLOOKUP function, and we'll explore them. You can imagine that VLOOKUP is very similar to someone calling customer service and asking the customer service representative to find information on their account.

Now, when the person calls customer service, the first question that representative will ask for is their account number. That account number is the lookup value. Using that lookup value, they can look up information in the database.

The database, in this case, is what we call the table array for the VLOOKUP. So, if we take a look at our information in the spreadsheet, this is the table of information we want to look up information from. The next argument is a column index number.

If the V in VLOOKUP stands for vertical, well, the column index number is the horizontal lookup that matches the column name for the field that you're looking for. If I'm looking for name, I want to match the column number for that header in the header section of the table. And then range lookup almost always is an exact match.

So, you could just type in false or zero, and I’d say 95% of the time you’ll get the value that you’re looking for. If you don’t, then you always have the option of choosing true or one for closest match. So, let’s take a look at an example of what you would do to look up the name for someone with ID B75.

I’ll type in equal, then I’ll enter a comma, and then I need to go to the table array. The table array is the database where the information is being kept. In this case, it’s going to be this table over to the right.

Now, when you select the table, you need to make sure that the lookup value is in the first column of the table array. What that means is the ID must be in the very first column of the table that I’m selecting. Now, this may not seem like a big deal, but sometimes the ID column is not the first column.

But for your VLOOKUP, it should be the first column you select when you select the table, even if it’s not the first column of the table. Column index number, I usually say all that’s required is your ability to count. What are you counting for? You’re counting for the column number for the field that has the value that you’re looking for.

The field that has the value that I’m looking for is name. So, as I look at the table, starting with the lookup column, I’m going to count one, two. So, I’ll enter the number two.

Notice it says column index number. It doesn’t say cell. It doesn’t say column, row, or table.

So, I’ll enter a number, and I’ll enter a comma, and then Excel gives me two options, true or false. Like I said, most of the times, default to just false, because you want an exact match for your lookup value. When you press Enter, you get Sally.

If I take a look at the table, I will indeed see that Sally is the name of the person with ID B75. In the class, we’ll give students an opportunity to do this themselves. They’ll look for the department for ID B43.

So, I’ll type in equal, VLOOKUP, then press Tab. The value that I’m looking up vertically is B43. I’m going to look that up in the first column of the table I’m selecting, and that first column contains a lookup value that’s ID.

This is where I need to count. I need to get the column number for department, which is the other coordinate. So, horizontally, not vertically, I’m going to count one, two, three, four.

So, that is the answer for the column index number. I’ll enter a comma, then I want an exact match. I’ll choose false.

When I press Enter, I see sales, and if I take a look at the table, sales is indeed the department for ID B43. A couple of things to look out for. The lookup value, as we explained earlier, must be in the first column of the table array.

When you’re working with VLOOKUP, if someone inserts a column into your table, that can throw the VLOOKUP off. What you refer to as the column index number for Sally is now incorrect because the column that has the value that you’re looking for is now column three, and column two doesn’t contain any values, and that’s what actually you’re returning. So, watch for that.

That is a limitation of the VLOOKUP common error. You don’t want to have duplicate values in your table. So, if I go to B44 and change it to B75, you’ll see that Sally changes to Bob.

That’s because VLOOKUP is going to look for the first value and find the match for the first instance of B75. I’ll press Ctrl + Z to undo. Also, lookup value must be identical to the table value.

Well, that’s pretty simple, but there may be situations where the values look alike but are actually different. For instance, if I add a space after B75 and press Enter, I get an error message. So, you may have to review the data that you’re getting to make sure it’s accurate and there aren’t any extra spaces.

You can always use the Trim function to remove excess spaces from a cell. All right, so let’s take a look at an example exercise where we’re working with a much larger table and we want to look up multiple values. So, I’ll start with customer, equal, VLOOKUP, tab.

My lookup value is 13601. I’ll enter a comma and then I’ll select the table. Ctrl + Shift + Down, selects the rest of the table, and then I’m going to enter a comma and this is where I count.

One, two, three, four, five, six, seven, eight, nine. I want an exact match. I’ll press Enter and when I AutoFill down, I should be able to get all the results except it looks like I didn’t get the result for 13419.

Why is that? Well, we’ll use our auditing skills. I’ll press F2 and what I now see is that the table has shifted and this is relative reference. The table should be the same as I navigate down through the values for the Order ID.

So, I need to go back to the original formula where I selected the entire table and lock it. I’ll press F4, press Enter. Now, I can AutoFill down and I will not have that error message.

So, I’ll learn from this for the sales rep, equal, VLOOKUP, tab. I’m going to select the Order ID, enter a comma, select the entire table. Starting from A20, I can press Ctrl + Shift + Right, Ctrl + Shift + Down, and now if I press F4, not only do I lock the sales, but I return to the formula without having to use my mouse.

I want the column number for sales rep. That’s one more column over than customer. So, that’s going to be 10 and I want an exact match.

I could also type zero. Zero is equivalent to false. I’ll press Enter.

When I AutoFill down, I will not get that same error message and I get all the correct values. For this last exercise, we just wanted to see if you were paying attention. There is my lookup value and if I start selecting the table using the Order ID column, this is where I’m making my first mistake.

The lookup value must be in the first column of the table array and we say that in the exercise for Practice 2. So, I’m going to select starting from the Order Date. Now, I’ll press F4, return right back to the formula.

This is where I count 1, 2, 3, 4, 5, 6. Enter a comma. I want an exact match. I’ll choose false.

When I press Enter, I get the right value. So, that’s how you could use VLOOKUP and avoid all the pitfalls that you might run into when you’re using it.