Excel Index Double Match

Advanced lookup functions are covered extensively in our NYC Excel classes. For those outside New York, find and compare the best Excel classes near you or online Excel classes.

Index Double Match 

INDEX can use both a row number and a column number in order to find its value. It'll efficiently return a value or a reference from a cell at the intersection of both the row and the column. 

Exercise

In this exercise, we'll do a quick review of our last INDEX MATCH exercise, and then we'll take a look at Index Double Match. 

In the last exercise, we made an assumption about where we were looking up the values. So we started off first with coming up with the row number for SKU08. So that's going to be equal, MATCH, this is my LOOKUP value. I'll enter a comma and where I'm going to find that LOOKUP value is in this one column. I'll enter a comma, and I will want an exact match. 

What this will do is it will look for SKU08 in that column and tell me what the row position is. I'll press Enter and I get eight. Now I want to find the part name for SKU08. So using INDEX what I can do is I can map out the territory. I'm gonna go over to the part name column, and then I'll enter a comma, and I want to find the value that's in the same position as SKU08. I can simply select the cell above, and that is the exact row position for Unicorn, which is the part name, so we're making an assumption here that we're looking for part name. 

Two-Way LOOKUP 

But let's say we wanted to perform a two-way LOOKUP in a table where we're not just looking for a row position, but also a column index number. This is much more like a VLOOKUP. 

Now, to start, what I'm going to do is name the table. I'm going to call this table "parts." That's an appropriate name for this table, because that's what it is, a table of parts. Now, I'll find the row number for SKU17, because what I'm looking for is the quantity for SKU17. That's going to be equal to MATCH, and this is going to give me the row number for SKU17 over in this column. 

Now I want to find the column number for quantity, so that's going to be equal to MATCH, and just like with the column index number, I'm going to select "Quantity." Then I'll enter a comma. I'll head over to the headers, and I'm looking for an exact match, I'll press Enter. I'll get three. 

Index Double Match 

Now, what I'm going to say about the Index Double Match function is it's very much like mapping software. When you're working with mapping software, what you're trying to do is find the longitude and the latitude. 

In this case, the row number is the longitude. We're going to move down a column and find the coordinate that determines the longitude of the index. Then the column number represents the latitude. 

So in VLOOKUP, this would be the column index number, you'd get a number of a column. The intersection of the row number and the column helps us find what we need within the grid. So in this case, what we did is we found the row number, which was 17. And that puts us right here. 

But that's not the only coordinate we need. We need to know what is the right column number. So if I take a look here, it says three. So this is one, two, three. And that is the right column number because it matches with quantity. 

So if I were to use Index Double Match, I would type in equal, INDEX, parts. It selects the entire area where I want to look in. 

The first coordinate I'm looking for is the row number. We already determined that over in this cell right above. Then I want to find the column number, and we already determined that using another MATCH function to get the column number for quantity. 

With those two coordinates within this grid, I get 999, which is exactly the value I'm looking for. Ultimately, with Index Double Match, what you would do is you would not rely on the other cells. You would actually type the MATCH functions in place of the cell referencing we just did here. 

So first, let's find the row number that's going to be MATCH. The row number is usually going to be the cell to the left, just like in VLOOKUP, that's the LOOKUP value. I'll enter a comma, and I'll vertically look down this column to find the row number. Exact match. 

Next, after I enter a comma, I need to find out which column am I going to find that value? So I'll type in MATCH just like with VLOOKUP when we're looking for the column index number, we're referring to the quantity. Then I'll enter a comma, select all the headers, and within that row is where I'm going to find the position number for quantity. Close parenthesis. Press Enter. I get 999. 

This gives you a little bit more flexibility because now that we're looking up the values for both column and row, we can change any one of them and pick up any value within the table. For instance, I want to find Unicorn. Well, for that I'm going to need to get the part name. Now I'm getting the part name for SKU17, but I'm actually looking for SKU08, so I can simply go here and type "08," and when I press Enter, I get Unicorn. That's because I'm looking eight rows down and one column across. 

Recap 

So that gives us some degree of flexibility when we're looking up information in a table. That is Index Double Match.