Creating a two-way lookup with VLOOKUP, HLOOKUP, INDEX, and MATCH functions

- [Instructor] Excel has a number of lookup and reference functions. And foremost among them are the V lookup function, it's companion H lookup, and also index and match. They're listed here in column A down at row 12. Now, in this particular worksheet, there's a list off to the right. And you can imagine this being much much larger with many many more items. And instead of months down here, maybe we might have weeks. So a model like this could be expanded easily. What we would like to be able to do, is to enter a month in cell B two.

Enter a product in cell B three. And then, return the dollar sales for that particular combination. So, May printers for example, we'd like to see this number 12,698. And the bigger the model, the more valuable this approach is going to be. So which of these functions is going to work? Invariably, it will take a combination of these. So we can approach this in different ways. One approach could be; let's find out where May is on the list. Let's find out where printers happens to be on the list.

And then use a function called index. The index function allows us to pull information out of a range like this based on a row reference and a column reference. So if we know where May is, and we know where printers are right here, we can see where they meet. So, let's approach this by going to cell B five, and find the month offset for May by using the function called match. Match says I've got an item, a value, in cell B two.

Comma. I'm looking for this in these cells right here. And since we possibly might even use the word total, up in cell B two, let's include that as well too. Comma. We want to find where that is in there. We need to find it exactly, so we put in zero. And the answer we get here is five. It's in the fifth position as we look at these cells here. Now with printers, we want to do the same kind of thing. But here we use the match function looking across a row instead of in a column.

We're trying to match that word printers, comma, with the data we see from K two over to P two. Here too, it needs to be an exact match. Zero. And that's in the fourth position as we look at these cells right here. Fourth position from left to right. Now, based on that information, we can use the index function here. Remember, the index function first refers to a table. A table of information. A range of cells. Perhaps more correctly. Comma.

Which row do we want to get the information out of? From wherever that month offset was. So we use cell B five. Comma. And which column out of this list? The column where we found the printers. And that's in cell B six. So the index function has all it needs. The location of the range, the row reference, the column reference. Enter. There's that value, 12,698. That's what we expected. So there's a look at the function right there.

Fairly compact, but we are using within this, data from the match function. Now, there's no rule or law that says we want to put these together, but we certainly could do that. I'm going to take this information here, highlight it, press control C, escape. And essentially, recreate it down here within pieces. Start with that, and for the moment, I'll just leave that there. But, let's take the function right here. Is it truly necessary to combine these? Not really. We don't need to do this.

Although, in one respect, we could ultimately not have the data in rows five, six, and seven there. But, to pull these together if we wish, highlight the data here, press control C, escape. Substitute that in here in this formula right here. B five. We'll paste it in there. Control V. So we're doing copy paste is all we're doing in the examples here. And then going back to cell B six. We'll highlight just that data there. Press control C. And escape. Then back to cell B nine.

We'll put in that leading equal sign to make sure it truly is a formula. And out here instead of this B six being here, we'll paste in what we just copied from cell B six. Control V. So it's a bit long, but we will get the same answer. And whether it's better to keep it as one formula, or to have it broken out in pieces, that's your call. But I think you can see how either one of these works. Is this the best way? Not necessarily. It could be. Another approach could begin with the V lookup function.

So for example here, what we could do here, is start with V lookup. Now, it's likely to be longer at first and may be a little more complex. But on the other hand, it may bring out some other aspects of how we could use this function. Let's begin here with the idea that we're going to start with the month from cell B two. Whatever's typed in there. Comma. And we want to find it's relative location in J two through all of this. Now, that might strike you as a little bit strange. But, V lookup is based on the concept that we're looking for information in the left column of a table or a range.

In this case, this entire range here. And then we're going to move right ward and pick up information based on a column reference. So, we're looking for May in column J. Even though this says J two colon P 15. Comma. Now we need to get the appropriate column reference. Now, we know that if we're looking at the data here, printers, but in this reference, that's in the fifth column over. So here, we're going to use the match function just slightly differently than the way we used it before.

We need to find that printer's reference. Comma. In these cells right here. Comma zero means exact match. So, this portion of the formula right here, if it's working properly, and it should be, is going to give us the answer of five. That's in the fifth position there, if we're looking at cells J two to P two. So, one more reference here at the end. Any time you're using V lookup for an exact match, we need this to be an exact match here.

Because we're trying to match up the word May, we put in either the word false or zero. Zero means exact match here too, or false. You can double click the false into place if you wish. Do it that way. Final right parenthesis. Enter. Same answer we saw before. So, it remains to be seen. I'll put a space in front of this so we can compare it with the other one. It's actually shorter than our other example up here. I'll double click that and put a space in front of that. So we can see it, and we see the combination. Now, earlier I had prepared this by way of H lookup.

I'll simply move this up here. And currently is white font so I'll expose it. And we see the combination. There's the same function. This would give us the same answer too using this. The only difference here is I used false instead of zero. But we could adjust that. So these would take up about the same space. So, all these functions ultimately give us the same results. It's just a question of what works best for you. What's easiest to understand. There's no question that they're powerful. And all of them allow us to pull information out of a list based on a month and a product entry.