Learn to use the VLOOKUP in this free Excel video tutorial.
VLOOKUP
VLOOKUP is a very popular function and Excel that allows you to look up information in a table that could be on the same sheet, on another sheet, or even in another workbook.
What is VLOOKUP?
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 the representative will ask for is their account number. That account number is the lookup value.
Inputs of VLOOKUP
Lookup Value
Using that lookup value, they can look up information in the database.
Table Array
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 this spreadsheet, this is the table of information we want to look up information from.
Column Index Number
The next argument is the column index number. If the V and V lookup stands for vertical for the column index number is the horizontal look up 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.
Range Lookup
Then range lookup - almost always is an exact match,so you could just type in false or zero and I'd say ninety five percent 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 1 for closest match.
Exercise
So let's take a look at an example of what you would do to look up the name for someone with IDP 75. I'll type in equal, then I'll type in the letters v l. I can then press tab and my look up value very similar to the account number for a customer. I'm looking up in this case order IDB seventy five.
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? Your 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 to notice. It says column index number. It doesn't say sell. It doesn't say column, row or table. So I'll have an interim 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 look up value when you press enter. You get Sally at my table. Take a look at the table. I will indeed see that Sally is the name of the person with IDB seventy five in the class.
Another Exercise
We'll give students an opportunity to do this themselves. They'll look for the department for IDB 43, so I'll type an equal v.l. then press tab. The value that I'm looking up vertically is b forty three. I'm going to look that up in the first column of the table I'm selecting and that first column contains a look of value. That's i_d_. This is where I need to count.
I need to get the column number four department just 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 all. Enter a comma. Then I want an exact match. And if I take a look at the table, sales is indeed the department for IDB 43.
Things to Lookout For
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.
Sally is now incorrect because the column that has the value that you're looking for is now column 3 and column 2 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 be 44 and change it to be 75, you'll see that Sally changes to Bob. That's because we look up who's going to look for the first value and find the match for the first instance of the seventy-five price controls 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 B seventy-five 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 Excel. 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 start with the customer equal to the L tab.
I look up values 13 6 0 1 0. Enter a comma. And then I'll select the table.
Control 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 and 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 third 13 for 19. 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 a relative reference.
The table should be the same as I navigate down through the values for the order i_d_. 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 v.l. tab Minna's select the order. I-D. Enter a comma. Select the entire table starting from A20. I can press control. Shift right. Control shift down. And now if I press F for not only do I lock the cells, 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 customers. So that's gonna be ten and I want an exact match. I could also type 0 0 is equivalent to false address. 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 i-d 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 too. So I'm going to select starting from the order date. Now I'll press F for return right back to the formula. This is where I count. One, two, three, four. Five, six. Enter a comma. I wanted the exact match, I'll choose faults when I press enter. I get the right value.
Recap
So that's how you could use VLOOKUP and avoid all the pitfalls that you might run into when you're using it.