Index/Match is a pretty popular formula combination that gives you the ability to move and manipulate data across Google spreadsheets, helping you to get the data you need faster. In order to understand how this works, let’s break things down.
What Does the INDEX Function Do?
Index returns the cell’s value by specifying the range to search, in addition to the row and column. The three arguments are reference, [row], and [column].
The reference parameter encompasses the data array that contains the value you’re looking for.
As far as the other two parameters, yes, the row and column are considered optional arguments, and if you omit them, you just return the full reference array.
How to Use the INDEX formula
Let’s take a look at the following scenario. You are in charge of renting high-end offices, conference rooms, and auditoriums to businesses.
If you are looking for the cost of Auditorium 3, for example,
The first parameter is the array of values you want to search. This does not include the headers or the column with the key you are using to search for the value (this is known as key/value pairing, and it’s a recurring theme in databases).
Next, you specify the row and column:
In this case, you’re looking at row 4. The column is 3, because it starts with the first column in the value array not in the entire table. Remember that, as it makes a big difference.
According to the table, this is correct, but this needs to be more dynamic, don’t you think?
Pairing INDEX and MATCH
Of course, that first example is the hard-coded way, which isn’t practical. This is where the MATCH function comes in. Now, I go into detail about the MATCH in my free Intermediate Spreadsheets Starter course, so it’s the same idea. In this case, we’re going to use MATCH to find that Per Diem Rate in a way that’s practical and reproducible.
We’re going to use MATCH to look for the specific office space. Remember, this is the INDEX formula: =INDEX(B2:D10,4,3)
The row parameter will be replaced by a MATCH function,
We integrate the MATCH function into the INDEX function,
and do the same thing to integrate the second MATCH for the column:
The difference here is that we use the headers that correlate with the data array we’re searching, so it’s the first row from column B to D. Now, we replace the 3 in the INDEX formula with this new MATCH formula making this an INDEX/ (Double) MATCH formula combination!
The beauty of this formula is that you can use it whether the table is sorted or not, even if the key values are in different locations than the value matrix. As long as the key column and the value columns have the same number of rows, INDEX/MATCH will work beautifully.
Quick note, when using this formula, remember to make sure your array references are absolute ($), so they don’t shift.
To see a live example, check out my video below