- Excel's powerful VLOOKUP function is often used for matching up data, or looking up data exactly. It also has a use, and you might have seen it in a previous movie, for an approximate match. Any time you're trying to match up data that's text, or it's an ID type number that must be exact, we use VLOOKUP in its exact match format slightly different than the other format for approximate lookups. In this worksheet called VLOOKUP Exact, it's in the workbook 10 - IF VLOOKUP COUNTIF, in column H, we need to know the department for each product we have in our list by the way, these products might look like they're repeating but they could be of different size, and or different color and off to the right we've got a list of all of our products a master list, which department each one is based in, and also the category.
Now to make this a little bit more visible on the screen I'm going to be hiding columns D, E, F, and G for the moment we don't need to see those. I'm going to drag across the column letters here, right click, and Hide. We can get those back later if we need them. And although column H doesn't really have to be wider I'm going to make it wider so we can see it as we enter the formula. We don't really need to see the size and color right now either, so I'm hiding those. Often you don't need to do this if you're working just by yourself, but for presentation purposes it's going to make some sense here.
And I can make column H even wider and then in the lower righthand corner in the zoom slider bar, click plus a few times, there we go. Alright, we're going to use a VLOOKUP here to find an exact match. We're looking for Bamboo Arm Chair over on the righthand side. And this is a list here of all of our different products we simply want to know which department they're in and possibly, eventually, category. So, =vlookup( here's the item we're looking up.
VLOOKUP is always based on the idea that what we're looking up, we're going to be comparing with a table located elsewhere. Now I'm using the word table in a very loose sense call it a list, call it just a range of cells, we've got a range of cells somewhere else it's off to the right, and we see that it's in columns K, L, and M and it's extensive, we might add to it later but for the moment we can refer to it simply by it's column letters. Now we do want to be getting data out of the second column there, the department column, in our result right here, so as we refer to the table, even though the comparison will be with Bamboo Arm Chair in the left column there we will be getting answers out of the second column so, as we refer to the table, we can simply drag across the column letters.
Now, this is provided that the only things we have in columns K and L are the headings, and all the individual entries below this no numbers, nothing else in that list down below. Comma VLOOKUP in any of it's different forms always requires us to indicate which column of this table, or this range of cells, has the answer and reading from left to right, column L is the second column in the table that we've indicated so we put in the number 2. Now, this is VLOOKUP used in exact match and when we're using it in this form, we need to have a fourth argument so following the indicator for the column reference we put in a comma, and notice the pop up, FALSE - Exact match now that might strike you as a little strange, as it does me now you can click this and tab it in place, or you can type it.
FALSE, that means exact match, really? Well, yes it does. Now, you can use 0 instead, FALSE by the way might suggest that something failed or didn't match but who would ever guess at first that it means exact match? I use 0 because it's less typing and there's another function that we won't be getting into called Match, that also uses 0 for exact match but you can use either one, so for the moment I'll use FALSE. Remember, FALSE means exact match, we must find Bamboo Arm Chair in the list over there in column K.
So, as I press enter, we should get an answer it's in the furniture department, and there we see it right down there, there it is, it's in furniture department. And because we used column references here, right here, we don't have to worry about making this an absolute address we can just copy this down the column. Now we can do this quickly with double click and we see the others there as well too. Now remember, these are not truly repeats we've hidden the columns that indicate that some of these are different sizes and possibly different colors.
For picking up the category, we want to do pretty much the same thing, and I can copy this and then adjust it. I'm simply going to copy this portion of the formula I'll press control-C, then just escape from here. Now, column H doesn't really have to be that wide so I'll make it narrower. And in column K we're going to do something very similar and here too I'll make it wider again, for display purposes, even wider. I'll type = and then paste in the function as we used it before, but some adjustments are needed what are we trying to look up? The value in A3, but now instead of looking at columns K through L, we're going to be looking at column K through M because we need to come up with the category from column M so instead of the second column here, that's what the 2 means, remember we put in 3, third column reading from left to right, and then we put in FALSE or 0, this time I'll use 0, could have used it before, we can use either one and Bamboo Arm Chair as we look at this on the list we're expecting to see this, dining, it's in the dining category, and there we see it.
And here, too, we can copy this down the column it looks as if there's a formatting feature that got copied we can deal with that a bit later, the point is we do have the correct answers. Now, because this means exact, either 0 or FALSE here, that means things can go wrong pretty easily. What if in typing this entry here, maybe it got copied from some other location, whoever typed it, maybe in typing it, put in a trailing space? Excel can't find that, so that's a problem you might have to clean up your data in column A there is a temporary workaround that gets into a function using an additional function that you might not have heard of, but I can clean it up here.
There is a function called Trim, t r i m. What Trim does, is it eliminates trailing spaces, leading spaces, but even when there are multiple spaces between words, it will reduce those to one. So this will work now, and I'll copy that down the column. Now also over here I'll introduce more spaces than are necessary. Now by the way, the really best long term approach here is to clean up column A rather than doing this but we'll have the same kind of a problem there notice over here these are failing too, I didn't adjust those, but I adjusted this one, and all of them in the column, and that's working fine too, even though there are extra spaces there.
Remember, it's trying to compare this with entries over here in column K, and over in column K, down in row 13, we see what's happening, there are no extra spaces on the end, and only a single space. So that's a big problem when you're comparing data and when we use an exact match, remember this is 0 or FALSE, data must match up exactly, that's what the word means, exact, and it's gotta be that way. So I've seen some variations on this for many people, using an exact match VLOOKUP is probably more common than using the approximate match.
You might have seen in the previous movie how we used VLOOKUP as an approximate match. We've seen different examples in this worksheet of how to match up data from different locations and again, I want to point out that from time to time, sometimes these lists are on different worksheets, a little trickier to work with when you're matching lists from different worksheets but that, too, you can do with this VLOOKUP function.
- Identify two navigational features within the Excel interface.
- Distinguish the best approach to managing data in cells.
- Recognize how Excel processes simple formulas.
- Describe the different ways to access formatting features.
- Identify page layout parameters for printing worksheets.
- Determine the best approach to creating charts.
- Explain how best to manage multiple worksheets and workbooks.