How to Use INDEX and MATCH Together in Google Sheets

When you need to perform a lookup, there are a couple of handy functions in Google Sheets.

VLOOKUP and HLOOKUP are great functions, but combining INDEX and MATCH together might just be the best way to perform a lookup in your spreadsheet.

In this tutorial, I will show you how to use the INDEX and MATCH functions together in Google Sheets.

INDEX Function

Before I go over how to use the two functions together, I will cover how to use each function individually so that you can understand how each one works.

The INDEX function returns the contents of the specified cell.

The syntax of the function is:

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

• reference – This is the range of cells that contains the specific cell you want to return
• row – This is the index of the row that you want to be returned within your reference range, this parameter is optional and will be 0 by default
• column – This is the index of the column that you want returned within your reference range, this parameter is optional and will be 0 by default

Here is a basic example of how to use the INDEX function

You can see in the example image above, my formula is set to “=INDEX(A1:B6,3,2)”. So the function returns the value in the 3rd row, second column of my specified cell range.

You can see now that this is a pretty simple function to use, as the name implies, you are just putting in the index of the cell you want to be returned into the function.

MATCH Function

The MATCH function returns the relative position of a value in a range that matches a specified value.

The syntax of this function is:

=MATCH(search_key, range, [search_type])

• search_key – this is the value that you want to search for
• range – the range of cells that you want to perform your search in. This function only works when used on one column or row. It will return #N/A! if you try to use it on ranges with multiple rows and columns
• search_type – this is an optional parameter that tells Google Sheets how to search. By default, it is set to 1
  • 1, this search_type will assume your range is sorted in ascending order. It will then return the largest value less than or equal to the search_key
  • 0 this search_type means exact match.
  • -1 this search_type will assume your range is sorted in descending order. It will then return the smallest value greater than or equal to the search_key

MATCH Function Example

To give you a better understanding of how the MATCH function works, let’s take a look at this example:

In the above example, I have the MATCH function set up I have used search_type 0. This means an exact match, so the formula returns a value of 2 because, in my range, the value 9 (my search_key) is in the 2nd row.

When you are using the MATCH function, search_type 0 is the most common one you will be using. Most often you want to find an exact match in your data, so this is what we have covered here.

Using INDEX and MATCH Together

Now that we’ve covered how to use the INDEX and MATCH functions individually, it’s time to take a look at how to nest them together to create a powerful lookup function.

To combine these two functions, you need to nest them together with this syntax:

=INDEX(reference, MATCH(search_key, range, match type))

As we covered earlier the syntax of the INDEX function is “=INDEX(reference, [row], [column])”.

The MATCH function returns the position of your search_key in the lookup range. So when you use the MATCH function as a parameter of your INDEX function, you can return the value at the same position as your search_key, but in a different lookup range.

This is why combining these two functions together works so great as a lookup function.

Here is an example of how to use this in your spreadsheet:

You can see in the above example my formula is “=INDEX(B2:B6,MATCH(D2,A2:A6,0))”.

Here is how this formula works:

1. The MATCH function is looking for my search_key of Van (D2) in my cell range of A2:A6.
2. Van is in the 2nd row of my cell range used in the MATCH function, so the MATCH function returns a value of 2
3. The INDEX function is now calculating like this: =INDEX(B2:B6, 2)
4. So the function searches in my column B range and returns the value in row 2, which is 9

By now, hopefully, you are beginning to understand how these are combined together to make a great lookup function.

But there is another way we can use them. We can use two MATCH functions together to search for multiple criteria. We will take a look at that below.

Index Match with Multiple Criteria

If you remember from earlier, the INDEX function can accept up to three parameters (reference, row, and column). In the previous example, we have used MATCH as the row parameter, but you can also nest another MATCH function in as the column parameter to search a range for multiple criteria at once.

I will show you how to do that by walking you through an example.

Here is the example data I will be using.

In this walkthrough, I will use INDEX and nest two MATCH functions together to search for the rating number by two criteria: employee and month.

Here are the steps to do this:

1. Firstly, select the cell where you want the returned value to calculate and press the equals sign (=) and type INDEX and then press Tab on your keyboard to start entering the function

2. Next you want to enter the range of data that want to search in. We are trying to return rating numbers and the data exists in the range of B2:C6. After you enter your range, add a comma (,)

3. Next you will begin your first MATCH function. Type MATCH and then press Tab. Then add your search_key. We are searching for the data in cell E2, so that is what we added. Add a comma (,) when you are done with this step

4. Next add in the range of data that contains the search_key you used in your previous step. In this example we are searching for a name in this MATCH function. The names are in the range A2:A6 so we have used that for this step. After you input the range, add a comma (,) and then put a “0” for exact match search_type. Add a closing parenthesis “)” and add another comma (,)

5. Now we have to begin our second MATCH function. type “match” and press Tab. From here you have to enter your second search_key. We are searching for the data in cell F2 (month). Add a comma after entering this (,)

6. Next add the range that contains your second search_key. We are searching for months, and in my data, the months are in B1:C1. After entering this range, add a comma (,) then a zero (0) and two closing parentheses “))”. Then press Enter to let your formula calculate

7. Your formula will how now calculated and this lookup function will return the value specified in your formula

The full formula I have used in this example is:

=INDEX(B2:C6,MATCH(E2,A2:A6,0),MATCH(F2,B1:C1,0))

Here is a breakdown of how this works:

1. The first MATCH function returns a value of 3 because, in my cell range of A2:A6, my search_key of E2 (Georgia) is in the 3rd row
2. The second MATCH function returns a value of 2 because my search_key in F2 (October) is in the 2nd column of my range of B1:C1
3. My INDEX is now set up like this =INDEX(B2:C6, 3, 2). So it is searching in cell range of B2:C6 and returning the value in the 3rd row and 2nd column, which is 9.

Closing Thoughts

Nesting these functions together may seem complicated at first, but after a little practice, you will develop a good understanding of how these work.

Try to replicate the examples we have covered in this tutorial and you should be well on your way to mastering this.

Once you have this down, it will become a powerful lookup tool for you to use in your own spreadsheets.

More Google Sheets Tutorials:
How to Use Data Validation
How to Use the IMPORTRANGE Function
How to Use Array Formula with VLOOKUP
How to Get the Last Value in a Column