The Google Sheets INDEX and MATCH functions, when used on their own, can seem to have limited applications. However, when combined together, they can be quite powerful. In fact, when together, they can provide a great (and actually better) alternative to the VLOOKUP function.
In this tutorial, we will discuss Google Sheets Index match, what each function does individually, and how they work when brought together. We will also discuss why we said that this master combo is actually better than VLOOKUP.
The MATCH Function
The Google Sheets MATCH function provides the relative position or ‘index’ of an item in a range of cells. It accepts a range of cells and a value, and returns the position of that value in the range of cells.
Syntax for the MATCH function is as follows:
=MATCH(search_key, range, search_type)
- search_key is the item that we want to match. This can be a text or numeric value, a cell reference, or a formula.
- range is the range of cells in which we want to search for an item matching the search_key.
- search_type is an optional parameter. It specifies the kind of matching we want. It can be one of the following values:
- 0: This value specifies that the search should be made for an exactly matching item. This option is usually used when our range is not assumed to be sorted in any order.
- 1: This is the default value. This option assumes that the range is already sorted in ascending order. Specifying this parameter as 1 returns the largest value less than or equal to the search_key.
- -1: This option assumes that the range is already sorted in descending order. Specifying this parameter as -1 returns the smallest value greater than or equal to the search_key.
Let us take a simple example to understand how the MATCH function works. Let’s say you have the following list of employee names:
If you want to know the position of the ‘Cierra Vega’ in the list, you can use the MATCH function as follows:
This will return the value 4, because the matching item is the 4th one in the list, starting from cell A2. Notice that 4 is not the row number of the matching item. It is the position or index of the item in the specified range (A2:A8).
The INDEX Function
The INDEX function provides the contents in a particular cell or range of cells. In other words, it accepts a range of cells, a row index, and a column index, and returns the value in the cell that is at the intersection of the specified row and column.
Syntax for the INDEX function is as follows:
INDEX(reference, [row], [column])
- reference is the range of cells from which we want to extract the item.
- row is the row offset within reference from which we want to extract the item.
- column is the column offset within reference from which we want to extract the item. This parameter is optional.
Let us take a simple example to understand how the INDEX function works. Let’s say you have the following list of employee names:
If you want to know the name of the employee who is at the 4th position in the list of names, you can use the INDEX function as follows:
This will return the value ‘Cierra Vega’, because that is the item at position 4 of column 1, starting from cell A2. Notice that 4 is not the row number of the matching item. It is the position or index of the item in the specified range (A2:A8).
Why use INDEX and MATCH Functions in Google Sheets?
As you might have noticed so far, the MATCH and INDEX functions don’t really provide much value as they are. But here’s the twist. When you combine the INDEX and MATCH Google Sheets functions together, they can do some very useful things!
When combined together the two formulas can look up a value in a cell from a table and return the corresponding value in another cell in the same row or column.
Consider the following dataset where you have a list of employee IDs, names, their departments, and monthly salaries:
If you want to access the salary of an employee, given his/her name, it might not be possible with a single function (besides a VLOOKUP).
This is where the INDEX-MATCH team can come in handy. With this pair of functions, you can access any employee’s department or salary, given their names, or any employee’s name given their ID.
In the following sections we will discuss exactly how you can achieve this.
How to Combine INDEX and MATCH Functions in Google Sheets
The general formula to combine INDEX and MATCH functions is as follows:
- search_key is the value we want to search for in range1.
- range1 is the range of cells from which the MATCH function finds the index for a value that matches search_key.
- range2 is the range of cells from which the INDEX function extracts a value corresponding to the position/index returned by MATCH.
In other words, the MATCH function helps the INDEX function identify the position of the value to return.
Let’s look at an example to understand this a little more.
Using INDEX and MATCH Function with Single Column References
In the following screenshot, you will notice we have a table consisting of IDs, names, departments, and salaries. There’s another small table at the bottom of the screenshot, where we have the name of one of the employees and a blank space for employee’s department:
If the name in the bottom table is dynamic (subject to change), then we cannot hard-code it into the formula that finds the corresponding department.
In this case, a combination of INDEX and MATCH functions will be ideal. Here’s the formula that we can use:
As you can see from the image below, this formula returns the department corresponding to the name ‘Cierra Vega’:
Explanation of the Formula
To understand how this formula worked, let’s break it down. We will start with the inner function of the formula:
This function looks in the range B2:B8 for value in B10, and returns the position of this value in the range. Since ‘Cierra Vega’ is the 4th name starting from cell B2, this function returns the position 4.
Next, let’s take a look at the outer function of the formula:
This formula looks for the value in the 4th position of range C2:C8 and returns the value at that position, which is ‘Sales’. This is the department ‘Cierra Vega’ belongs to.
Now, even if we change the name in cell B10, we would still get the right result in cell B11:
Thus, the INDEX-MATCH functions helped us use one value to get access to another value in a different column, in the same row.
Using INDEX and MATCH Function with Multiple Criteria
The above example accessed a single column as its range. However, the INDEX-MATCH functions can also provide more flexibility by letting us access a value from multiple columns.
In our example worksheet, let us say we can have either Department name, Salary, or even ID as a label in cell A11. In other words, what if the label in cell A11 were also dynamic and subject to change?
In that case, we would need to consider multiple columns and the column we access will depend on the label in cell A11. Well, this can also be achieved using INDEX-MATCH, as follows:
This time we also made use of the third parameter of the INDEX function (which lets us specify a column index in the range).
As you can see from the image below, if cell A11 contains the text “Salary”, this formula returns the salary corresponding to the name specified in cell B10:
If cell A2 contains the text “ID”, this same formula returns the monthly ID corresponding to the name specified in cell B10:
Even if you change the name in cell B10, you would still get the correct ID:
Explanation of the Formula
To understand how this formula worked, let’s break it down. We will start with the first MATCH function:
As explained before, this function returns the position of Cierra Vega’s name in the range B2:B8. So it returns the index, 4.
Next, let’s take a look at the second MATCH function:
This function looks in the range A1:D1 for the value in cell A11, and returns the position of this value in the range. When A11 contains the text “Department”, the function finds that this is the 3rd item starting from cell A1. So, this function returns the position, 3.
Finally, let’s look at the outer function:
This formula looks for the value in the 4th position row-wise and 3rd position column-wise in the range A2:D8 (with a row and column offset of 4 and 3 respectively) and returns the value at that position, which is ‘Sales’. This is the Department corresponding to Cierra Vega.
So now we have a lot of flexibility to go ahead and change the name that we want to access as well as the information that we want for that name.
Why is Using INDEX and MATCH Better than Using VLOOKUP?
After reading the entire tutorial you might be thinking:
“Couldn’t all this be done with a VLOOKUP function?”
Well, it could! In fact, what INDEX-MATCH does is basically a VLOOKUP. However, there are certain things that INDEX-MATCH can do that VLOOKUP can’t.
- INDEX-MATCH allows looking up columns to both the left and the right of the given search column, whereas VLOOKUP only allows looking to the left of the search column. It searches the first column in the given range and then looks for matches to its right only. Trying to access a column to the left of the range with VLOOKUP returns a #N/A error.
- Adding new columns or moving existing columns don’t affect the results of the INDEX-MATCH formula because it accesses cell references. So the order or change in index of the column does not matter. VLOOKUP results, however, get completely messed up, if a column is changed or removed, because it accesses column order, rather than cell references.
To conclude, the Index Match Google Sheets function provides a great alternative to VLOOKUP by adding greater flexibility to lookup operations involving dynamic cell references. We hope this tutorial has been helpful and easy for you to understand and follow.