INDEX MATCH Google Sheets: Easy Guide

I can’t overstate the value of INDEX MATCH in Google Sheets. While there are several functions designed to look up data, these are two of the most powerful. Used on their own, the INDEX and MATCH functions have limited applications. However, they become exceptionally powerful when combined.

The INDEX MATCH function helps me evaluate a data set in one column or row, and then return data from a corresponding row or column based on my query. Sound complex? It’s not. I’ll explain INDEX MATCH with specific examples below.

Why Use the INDEX MATCH Google Sheets Function?

As you might have noticed so far, the MATCH and INDEX functions don’t provide enormous value on their own. But here’s where it gets interesting. When you combine the INDEX and MATCH Google Sheets functions together, they can do some very useful things! When combined, 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. For me, that’s a huge timesaver.

My video shows how to use INDEX MATCH in Google sheets. Prefer to watch it on YouTube? I have it listed there too.

It always helps to consider real-world examples. So let’s use the following dataset. It has a list of employee IDs, names, departments, and monthly salaries:

If you want to access an employee’s salary, given their 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. Note that this kind of data can also be concatenated if you have separate columns for employee first and last names.

In the following sections, I’ll show you exactly how to use index match in Google Sheets to achieve this.

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. Note that this is a smart alternative to the VLOOKUP function, which also helps you quickly find data in Google Sheets.

To use INDEX MATCH in Google Sheets, we’ll need to discuss the syntax for each function. The syntax for the MATCH function is as follows:

=MATCH(search_key, range, search_type)

Let’s break that down into each of the key terms. Inside MATCH:

• 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 done 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’s 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:

=MATCH(‘Cierra Vega’,A2:A8,0)

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 in Google Sheets 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. As we did with the MATCH function, I’ll break down the INDEX function into component parts. This helps us understand what we’re looking for when we use INDEX MATCH in Google Sheets.

Ok, let’s consider what makes up this function. The syntax for the INDEX formula is as follows:

INDEX(reference, [row], [column])

 That means that, inside INDEX, we need to call the following:

• reference is the range of cells from which we want to extract the item.
• row is the row offset within the reference from which we want to extract the item.
• column is the column offset within the reference from which we want to extract the item. This parameter is optional.

Here’s an 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 in the 4th position in the list of names, you can use the INDEX function as follows:

=INDEX(A2:A8,4,1)

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).

How to Combine INDEX and MATCH Functions

I broke down the individual functions into their syntax earlier, so let’s do the same for INDEX MATCH in Google Sheets. Like the individual functions, this has its own syntax. Here’s what it looks like:

=INDEX(range2,MATCH(search_key,range1,0))

The INDEX function contains the MATCH function. The rest of the syntax break down as:

• 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. So let’s look at an example.

Using INDEX and MATCH Functions 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 the 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 are the index match formula Google Sheets that we can use:

=INDEX(C2:C8,MATCH(B10,B2:B8,0))

Here’s how:

1. Click on the cell you want to return the match to
2. Type the equals sign, then INDEX(
3. Select the range we’re looking up and the value we want to return. In our example, that is in column C. Add a comma after.
4. Type Match and select it from the suggestions.
5. Choose the cell with the value we’re looking for. In our case, we’re looking for Cierra Vega’s department, so B10 is the cell reference.
6. Select the range we’re looking for the keyword in. In our example, that is in column B.
7. Type in 0 to return an exact match and close the brackets. Then click Enter.

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 works, let’s break it down. We will start with the inner function of the formula:

MATCH(B10,B2:B8,0)

This function looks in the range B2:B8 for a 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:

=INDEX(C2:C8,MATCH(B10,B2:B8,0))

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 Match with Different Sheets

If you have data sets in different sheets, you can still use index-match Google Sheets on different sheets.

All you have to do is add the sheet name when referencing the column you want to look up.

In our example, we have two sheets: one for sales and the other for the price of each pair of shoes. Here’s how to lookup values from different sheets using the index match function:

1. Create a new table for the criteria you want to look up

2. Type in the index formula

3. For reference, go to the other sheet and select the column you want to look up.

4. Add the match function for the row argument, in our case its

=INDEX(Sales!D2:D11, MATCH(F2,B2:B11,0))

This formula returns the value from the Sales sheet to the second sheet.

Using INDEX and MATCH Function with Multiple Criteria

The above example accessed a single column as its range. However, the  INDEX-MATCH functions can provide more flexibility by allowing us to access a value from multiple columns. This is by using the Google Sheet index match multiple criteria formula.

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 labels 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:

=INDEX(A2:D8,MATCH(B10,B2:B8,0),MATCH(A11,A1:D1,0))

Here’s how:

1. Click on the cell you want to return the match to
2. Type the equals sign, then INDEX(
3. Select the range we’re looking up the value we want to return. In our example, that is in column C. Add a comma after.
4. Type Match and select it from the suggestions.
5. Choose the cell with the value we’re looking for. In our case  we’re looking for Cierra Vega’s department so B10 is the cell reference.
6. Select the range we’re looking the key word from. In our example that is in column B. Add a comma after.
7. Type the second MATCH formula with the second key word we want to lookup in the second column.
8. Type in 0 to return an exact match and close the brackets. Then click Enter.

This time we also used 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 will still get the correct ID:

Explanation of the Formula

To understand how the Google Sheets index match multiple criteria worked, let’s break it down. We will start with the first MATCH function:

MATCH(B10,B2:B8,0)

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:

MATCH(A11,A1:D1,0)

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 index match spreadsheet function:

INDEX(A2:D8,MATCH(B10,B2:B8,0),MATCH(A11,A1:D1,0))

This index match formula in Google Sheets 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.

Case-sensitive VLOOKUP with INDEX MATCH in Google Sheets.

When using VLOOKUP, it usually returns the first name it finds, regardless of the case. If you’re working with case-sensitive data or want to differentiate two similar things with the case, you can use INDEX MATCH instead. This is because, unlike VLOOKUP, the Google Sheet index match usually returns an exact match. Remember that there’s also a VLOOKUP in Excel.

However, you will need to use the index match sheets function Google Sheets together with FIND or EXACT.

Case-Sensitive Index Match Function Using EXACT

For example, in our sheet below, we have several appearances of the word Nike. However, they have different cases.

Explanation of the Formula

If we want to look up Nike with an uppercase letter, we can use the following formula:

=INDEX(D1:D10,MATCH(TRUE,EXACT(F2,B1:B10),0))

1. In the formula, we started with our index formula. Our reference D1:D10 is for the value we want to return.
2. Since we don’t know the row where our value is, we insert the MATCH formula here after a comma. Our search key for the match formula is TRUE which.
3. In the range for the match formula, we use the EXACT formula first, then the cell reference for the Nike we want to look up or the word Nike itself in quotation marks.
4. B1:B10 is the range that we are looking up the word Nike in.
5. Finally, we close the brackets and add the third argument 0, for an exact match.

With this formula, we can look up the word Nike with the uppercase letter in column B and return the value it matches in column D.

Case-Sensitive Index Match Function Using FIND

The FIND function walks almost the same as the EXACT function. The only difference is that instead of the search key TRUE, we use the number 1 as the first argument.

For our example sheet, we will use the formula:

=INDEX(D2:D11,MATCH(1,FIND(F3,B2:B11)),0)

This will give us the same result as when we used the EXACT formula with match index Google Sheets.

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 doesn’t affect the results of the INDEX-MATCH formula because it accesses cell references. So the order or change in the 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.

Related: VLOOKUP from Another Sheet

Frequently Asked Questions

Can You Do INDEX MATCH on Google Sheets?

Yes, you can do an INDEX Match on Google Sheets under the following syntax:

=INDEX(range, MATCH(search_key, range, [match_type]), [column_num])

Is INDEX MATCH Faster Than VLOOKUP in Google Sheets?

The INDEX-MATCH combination function is considered to be faster and more flexible than the VLOOKUP function in Google Sheets. This is because it does not need to search for the value in the leftmost column of the range, which can be time-consuming, especially if the dataset is very large.

Meanwhile, the VLOOKUP function is limited to searching for a value in the leftmost column of a range and returning a corresponding value from a specified column to the right of that column.

This makes INDEX-MATCH more flexible and useful in many scenarios, especially when you are working with large datasets.

How Do I Use INDEX and MATCH Instead of VLOOKUP?

In the cell where you want to display the result, use the INDEX function to specify the range of data and the column from which you want to return the result. Use the MATCH function to find the position of the value you want to search for in the range of data.

Combine the two functions using the following formula:

=INDEX(range, MATCH(search_key, range, [match_type]), [column_num])

This will search for a value in the search key range and return the value from the third column in the row where the match is found.

How Do You Do an INDEX MATCH With Two Matches?

To perform an INDEX-MATCH with two matches in Google Sheets, you can use the following formula:

=INDEX(return_range, MATCH(match_1, lookup_range_1, 0), MATCH(match_2, lookup_range_2, 0))

The INDEX function then returns the value at the intersection of the row and column that correspond to the positions returned by the two MATCH functions.

How Do I INDEX and Match Multiple Columns?

If you want to use the index match function in Google Sheets to look up multiple columns. You simply need to use the method for index match multiple criteria Google Sheets. This is done using MATCH in the third parameter of the INDEX function (which lets us specify a column index in the range).

In our example above, we used the formula:

INDEX(A2:D8,MATCH(B10,B2:B8,0),MATCH(A11,A1:D1,0))

The third argument of the index formula is usually meant for the column. In this case, we used the formula MATCH(A11,A1:D1,0) for the column argument to use multiple columns instead of one.

Conclusion

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. This is just one of the many ways to search in Google Sheets. 

Related:

• • Using Query Function in Google Sheets – with Examples.
  • Remove Last Character from a String in Google Sheets
  • Conditional Formatting Based on Another Cell Value in Google Sheets
  • Ultimate Guide to Conditional Formatting in Google Sheets
  • How to Zoom In and Zoom Out in Google Sheets
  • Creating a Heat Map in Google Sheets (Step-by-Step Tutorial)
  • How To Format Phone Numbers In Google Sheets?