Index and Match are a powerful method to look-up data from a table, in a similar way to a VLOOKUP or its sibling formula, HLOOKUP.
Using INDEX and MATCH together allows you to look-up data in a more flexible manner, and in a way that prevents the breaking of formulae due to adding/removing columns and rows.
INDEX accepts three arguments, the first is the table (or array) you want to find data from, the second is the column number you want the data to come from, and the third is the row number you want the data to come from. It outputs the resulting cell’s value at the intersection of the column number and row number within the first argument’s array.
If the first argument of the INDEX statement is a single column or row, you can omit either the second or third argument respectively by leaving it blank.
MATCH accepts three arguments, the first is the value (or cell) you are trying to find a match with, the second is the column/row (or array) you are looking within, and the third is the type of match, less than (1), equal to (0), or more than (-1). You will almost always want to leave this argument as ‘0’ when using MATCH in an INDEX statement. Its output is a count of the number of cells within the second argument it took to reach the value you were trying to find a match with, i.e., if your match is the third cell in the column you are looking in, then MATCH will output a 3.
Examples
Here we have a data table, with some information we would like to look-up to create some summary data, use the link below to download the file to follow along.
Index and One Match (Looking up a particular column)
In our first example, we will look-up the cost of a particular part (part E).
The formula will look as follows: =INDEX ($B$2:$B$8, MATCH(“E”, $A$2:$A$8, 0))
The first argument ($B$2:$B$8) corresponds to the ‘Cost’ column of our data table. The second argument, is how we will find out the cost of part E. In the MATCH statement, the first argument is “E”, the part we are trying to find out the cost of, the second argument ($A$2:$A$8) is the ‘Parts’ column we want to look through, and the final argument is ‘0’, meaning we want to look for an exact match.
This will return the output £0.23.
Index and One Match (Looking up a particular row)
In our second example, we will look-up a particular piece of data (Quantity) corresponding to a number of different parts (A – C).
The formula will look as follows: =INDEX ($B2:$D2,, MATCH(“Quantity”, $B$1:$D$1, 0))
The first argument ($B2:$D2) corresponds to the first row of data, matching the data of part A. When we drag the formula down, because the ‘$’s are in front of the column and not the row number, the formula will change to the second row of data then the third and so on, corresponding to parts B – C. The second argument is blank, as we are only looking at a single row of data, so do not need a row number. In the final argument, our MATCH statement, the first argument is “Quantity”, the piece of data we are trying to find for each part, the second argument ($B$1:$D$1) contains the row headings of our data table we are going to look through, and the final argument is ‘0’, meaning we want to look for an exact match.
This will return the output of:
A 7
B 89
C 19
In both examples, we can change the data we are looking up from being hard-coded (“E” and “Quantity”) to looking at a cell that contains that information, allowing us to link the formula to something like a dropdown list (as shown in the example file).
Index and Two Matches (Looking up data from a table, matching a particular column AND row)
In our final example, we will look-up a particular piece of data (Unique ID) for a particular part (F).
The formula will look as follows:
=INDEX ($B$2:$D$8, MATCH(“F”, $A$2:$A$8, 0), MATCH(“Unique ID”, $B$1:$D$1, 0))
The first argument ($B$2:$D$8) corresponds to all of the data within the table, starting from the ‘Cost’ column (B). The second argument is our first MATCH statement. Within this MATCH statement, our first argument is “F”, the part we are finding the data for, the second argument ($A$2:$A$8) is the ‘Parts’ column we want to look through, and the final argument is ‘0’, meaning we want to look for an exact match. The third argument is our second MATCH statement. Within this MATCH statement, our first argument is “Unique ID”, the type of data we are looking for, the second argument ($B$1:$D$1) contains the row headings of our data table we are going to look through, and the final argument is ‘0’, meaning we want to look for an exact match.
This will return the output: A0006.
Once again, we can change the data we are looking up from being hard-coded (“F” and “Unique ID”), to instead look up two cells that contain the information, allowing us to link to formula to something like a dropdown list (as shown in the example file).
Static Interest 