Index Match Google Sheets (Must Have Search Skills for 2023)

Google Sheets has several look-up functions that all work differently. Among them are the INDEX and MATCH functions.  When used on their own, INDEX and MATCH functions have limited applications. However, when combined, the index match Google Sheets function can be quite powerful. In fact, 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.

Lookup functions like INDEX and MATcH can be a little tricky for beginners. If you’re having trouble, we recommend trying out a beginner’s course to get your bearings.

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.

The syntax for the MATCH function is as follows:

=MATCH(search_key, range, search_type)

Here,

  • 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 us take a simple example to understand how the MATCH function works. Let’s say you have the following list of employee names:

index match Google Sheets

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

Datacamp ad

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.

The syntax for the INDEX formula is as follows:

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

 Here,

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

Let us take a simple example to understand how the INDEX function works. Let’s say you have the following list of employee names:

index match Google Sheets

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

Why Use the INDEX MATCH Google Sheets Function?

As you might have noticed so far, the MATCH and INDEX functions don’t really provide as 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, departments, and monthly salaries:

index match google sheets

If you want to access an employee’s salary, 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 to use index match in Google Sheets to achieve this.

How to Combine INDEX and MATCH Functions in Google Sheets

The general formula to combine INDEX and MATCH together is as follows:

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

Here,

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

index match google sheets

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

As you can see from the image below, this formula returns the department corresponding to the name ‘Cierra Vega’:

index match google sheets

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:

index match google sheets

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.

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
Create a new table for the criteria you want to look up
  1. Type in the index formula
Type in the index formula
  1. For reference, go to the other sheet and select the column you want to look up.
For reference, go to the other sheet and select the column you want to look up.
  1. 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.

Index match a seperaate 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?

how to index match google sheets

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

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:

index match google sheets

If cell A2 contains the text “ID”, this same formula returns the monthly ID corresponding to the name specified in cell B10:

index match google sheets

Even if you change the name in cell B10, you will still get the correct ID:

index match google sheets

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.

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.

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.

With this formula we can lookup the word Nike with the uppercase letter in column B and return the value it matches to 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.

Using FIND to give 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

Is INDEX Match Faster than VLOOKUP Google Sheets?

If you don’t have a large data set, you might not notice it, but Google Sheets match index runs faster than VLOOKUP. This is one of the advantages it has over the VLOOKUP function in Google Sheets.

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

index match google sheets

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. We hope this tutorial has been helpful and easy for you to understand and follow.

Related:

Spreadsheet Expert at Productivity Spot | + posts

Sumit is a Google Sheets and Microsoft Excel Expert. He provides spreadsheet training to corporates and has been awarded the prestigious Excel MVP award by Microsoft for his contributions in sharing his Excel knowledge and helping people.

Popular Posts
You May Also Like