Discover Excel's INDEX and MATCH functions to perform dynamic two-way lookups, efficiently pinpointing data at the intersection of rows and columns. Enhance your data retrieval skills with step-by-step examples demonstrating INDEX double MATCH's versatility and precision.
Key Insights
- The INDEX function, combined with two MATCH functions, allows users to perform precise two-way lookups by identifying cell values at the intersection of specified rows and columns, similar to latitude and longitude coordinates.
- Using named tables, such as naming a range "parts," simplifies and clarifies the INDEX double MATCH process, enabling efficient retrieval of values like locating the quantity "999" at the intersection of SKU17 and the quantity column.
- Employing INDEX double MATCH instead of traditional VLOOKUP provides greater flexibility; for example, users can quickly switch lookup criteria from SKU17 quantity to SKU08 part name ("unicorn") without altering the core formula structure.
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.
In this exercise, we'll do a quick review of our last index exercise, 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. 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'll 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 going to 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. We're making an assumption here that we're looking for part name. 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.
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. If I take a look here, it says 3. So this is 1, 2, 3, and that is the right column number because it matches with quantity.
If I were to use index double match, I would type in equal index parts 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 parentheses, 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, and 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. So that gives us some degree of flexibility when we're looking up information in a table.
That is index double match.