Join Dennis Taylor for an in-depth discussion in this video Two-way lookup using the MATCH and INDEX functions, part of Excel Tips Weekly.
- In this worksheet, a variety of Excel features are being pulled together to give a somewhat vivid display of finding information based on a list in Column C perhaps this is inventory, it could be much larger or smaller, and also some states across the top, not all of them are listed, it could grow, could include others. Of course it could be used for different examples too, cross monthly data, and what's also set up here are some Pick Lists, so that in for example, A6, we can click a drop arrow and see our list of inventory.
We don't have to do any typing here, we'll scroll up and down and pick an item, and as soon as we do watch the yellow row to the right move downward. We can also vary this by state. This is also a Pick List, these are easy to set up and we'll show you how. And when we make changes to either one of these, we also see a total up top, this change. This is using an index function. So also involved in the mix of features here is an index function, two uses of the Match function, we've already mentioned the Pick List idea. Those are features based on data validation.
And we also have some conditional formatting going on here. So whenever we do come up with this number, for example 2447, we also see a yellow column and a yellow row and where they intersect, we're seeing the same number. So it just facilitates the idea, it's going to make it easier to find these numbers, and also for presentation purposes, it makes the numbers jump out at us. So this is on a certain worksheet. To the left of this worksheet is the same data, but without any of the features. And so we're going to be showing you how each of these can be applied, starting with Data Validation, the Pick List idea.
So in column A, and I've arbitrarily done this in column A, it could be done anywhere, in this list to the right let me zoom back a little bit and scroll around just to see what we've got here, we've got data across columns C through X, and moving downward our inventory list, and below this and off to the left we've also got a list of the states here, they're alphabetized. The list up top, they're in order more or less by population, left to right. Not all the states are there, of course, some of them might be added later.
The Pick List that we're going to use here could refer to either one of those lists. It's just a question of convenience. And looking for state names, are you likely to more often see California, Texas, Florida, because you're looking there more often, they have higher population? Well, maybe, maybe not. If you are, maybe you'll use a Pick List based on this order, or possibly the alphabetical order down below. Either one. But I want this to exist in Cell A11 right here. So on the Data tab, Data Validation, click the button.
And in the Settings tab, instead of allowing Any value, let's base this on a List. Click in the Source panel and go highlight the list. I'm going to use the list up above, so I'm going to be highlighting cells D1, doing this in the background, dragging across, and even though the word "Total" is not a state, I want to include that as well too. Because it could be one of our choices. Through cell X1, and Enter. And so now in the Pick List that's been set up right here for State, we'll click the drop arrow, we can scroll up and down, pick the state we want.
Nothing is being highlighted just yet. We're not using conditional formatting, we're just setting up the Pick List for State. Let's do the same thing for Item right here. Same general idea, Data Validation, Allow List, and here too we're picking a list. This also has been alphabetized ahead of time, that would be a good idea, probably the easiest way to find items in the list. Starting at C2, down to, and here too I'll use the word Total, because it could be one of our choices. Through the word Total. And OK. And just a quick check on that.
Here's the drop arrow, pick Dresser, see what's happening or this one. Again, nothing is happening to the right, within the numbers just yet. In order to do that, we need to set up what's called an Index function, and we're going to be using the Match function. The Index function, a brief description would be we're looking at a table, say the data over here and we want to pull out data based on a row location and a column location. So let's use the Match function here first of all to indicate which row this is found in. =match( we're looking at this item that currently is in cell A6.
Comma, where might it be? It's in column C, comma. Notice the pop up tip here, 1, 0, -1, we want this to be an exact match. We want to find the item exactly. Don't need to type the right parenthesis in the formula, there it is, it's in the 10th position the Bamboo Desk/Shaker Drawer. Looking here and it matches the row number because we selected the entire column. In order to make that visible so we can see it all the time, and this is not critical, but I'm going to use a function new in Excel 2013 called Formula Text.
Soon as you see it appear here as you type it you can Tab it into place. This simply says Display the formula in another cell. So we can see that. So here's the formula in cell A3, and let me zoom in on that a little bit too so we can see it better. The formula in cell A3, that gives us the answer 10 is this right here. It says "Match the current value that we have in A6 "tell us where it's found in column C." Similarly, we want to do the same kind of thing down here for the State, so =match( currently it's Washington, we can change that of course we want to choose Washington, comma drag across the location of those state names.
Here, too, we want an exact match. Comma, 0. There we are. And since I used Formula text here to refer to the cell above it, with the Control key I can drag this downward and put it right under the 13 to also expose that formula. Once again, we're simply using the Formula text function to display the formula above it, for reference purposes. Remember, I can change this. We'll see the number 10 change to something else. 11, scroll up and down here, pick something else. The comforter, there it is right there.
Row 8. And down here with State, we can do this too. We'll get a different number instead of 13 if we pick a different state. Maybe I'll scroll up the list this way. There's Florida, position 3. Now the Index function, as I suggested, begins with the idea of we're looking at a table somewhere, or a range of cells perhaps is the better way to say it. We're looking at a range of cells from column D through column X. And we want to pull out information based on a row indicator and a column indicator.
The row indicator, in other words, which item it comes from, we can use our formula that we created in cell A3. So I'll simply click A3, the column indicator, and that's going to be the state, we'll use what the Match function gave us as we used it in cell A8, so I'll simply click A8. And as I press Enter, if we're looking at Bamboo Comforter for Florida here, look at the numbers to the right. We're expecting to see the number 1,919 as I press Enter. And that's it. Now with regard to the highlighting, which I think would bring out the data much more clearly, and ultimately of course we would hide this and hide this or just get rid of it, we wouldn't really need to see that, and these two functions that we're using here don't need to be displayed, we can put them somewhere else.
Another option would be to actually copy this match right here, escape, and actually paste it in here. So this could be the entire Match function. Also the same thing over here. That would make the function quite a bit longer, but then we wouldn't need the other two functions because they would all be embedded here. So we can approach that different ways. Now for the conditional formatting to work what we're saying, if we want this to match up with our first view of the other data, any time the item selected is this, we want the entire row to be highlighted all the way across.
And any time a certain state is chosen, we want the entire column to be highlighted as well. So by way of conditional formatting, let's select the data we want to highlight the columns C through X. Now if we don't want the yellow column to exist all the way down the worksheet, possibly we would only be highlighting this data like this. I'm going to use the entire column because maybe that inventory list might grow a bit later. But you can approach it different ways and want to experiment with how that's going to look. So here's what we're about to do with conditional formatting.
We're going to be saying in effect, if one of these items is selected, make that entire row be yellow. And if one of the states is selected, as is the case in our list over here, we want the entire column to be highlighted as well. So these cells are highlighted. And you can use conditional formatting in a lot of different ways, but none of the standard methods will apply here. So we create a new rule in conditional formatting. Use a formula to determine which cells to format. Now what we want to say here is, first of all, if the entry is the same as the item that we see over here.
Now, oddly enough we use the cell reference C1 because that currently is the active cell. If we want to be looking simply at the item, it's always going to be in column C. So as we press the Function key F4 here, we're only looking in column C. What you have to visualize here, and it's a little bit tricky, if we're for example considering cell M15 or M14, any cell like that, always be looking in column C for the item. So any time the reference in column C is equal to the item that we're considering, and that's in cell A6, we want highlighting to occur.
What we also want to have happen is, any time C1 reference, but this time only in row 1 so we'll press F4 again so that the dollar sign is only in front of the row reference. Whenever that is equal to the current state that we've selected, and that is cell A11. Now when either of these exist, so right after the = sign we want to put in the function OR( so when either of these conditions is true, now again invariably as you try these you're likely to make a mistake so come back and reconsider it.
I'm going to highlight this and display it larger on the screen in a moment, for the moment I'll just copy it. Then we apply a format. We could fill this, I'm going to fill it with yellow. If we also wanted the cells to stand out a little bit better we could go to the Border feature and choose an outline like this, wouldn't have to be black necessarily, but we could certainly do that. Click OK. And click OK. And there we see what's happened. Let's switch the state here. Here's Florida, I'll switch that to Pennsylvania, move to the right a few columns, I'll change the item here, and of course sometimes based on the item chosen you'll have to scroll down and find it.
In this case just a little bit possibly, but we see what's happening here. I think you can see too that the conditional formatting although not exactly required here does bring up the data, and for certain kinds of display reasons you're looking at these totals, you want them to stand out more prominently, here's some ideas here. But it's not always the case that you would be using all these features at the same time, but just as a reminder, we initially set a Pick List here in cell A6 and A11 to get to our items and states, then we use the Match function and the Index function to give us row numbers and column numbers, and then we put that together with the Index function to be able to isolate the number in question.
And following that we used conditional formatting. So it's not uncommon to be using a variety of different features in Excel to come together with a display package, you might call it, as we've seen in the examples here.
Note: Because this is an ongoing series, viewers will not receive a certificate of completion.
Skill Level Appropriate for all
Q: Why can't I earn a Certificate of Completion for this course?
A: We publish a new tutorial or tutorials for this course on a regular basis. We are unable to offer a Certificate of Completion because it is an ever-evolving course that is not designed to be completed. Check back often for new movies.