How To Use INDEX and MATCH Together in Google Sheets

The INDEX and MATCH together in Google Sheets come useful if you want to perform lookups.

Using the `INDEX` and `MATCH` together in Google Sheets performs similarly to like `HLOOKUP` and `VLOOKUP`, but even better (which we will get into later in the post below). This function requires two functions which are the `INDEX` and `MATCH`.

Now, yes, one is nested inside another, which may lead some users to find them hard to use, but having complex functions is the gateway to more advanced use of the Google Sheet formulas.

Let’s take a closer look at the formula by breaking it down:

The `INDEX` function is one of the mostly-used functions in Google Spreadsheet. It gets the value at a given location in a list or table. For example, you have created a seating plan for the class, and you want to get the first name of the 8th student with a formula. Using the `INDEX` function, you can do as follows =INDEX(A2:A11, 8)

Upon hitting the Enter key, it will give you the resulting value, “Mark.”

In the given situation, we are trying to find the first name of the 8th student in the class. Using the `INDEX` function, we selected the values under the First Name column, which will be the cell range A2:A11. Since we are looking for the 8th student, we, therefore, placed the number 8 after the comma (separator).

You will better appreciate the `INDEX` function once, especially when you are dealing with thousands of information, and you have to look out for a specific position. 🙂

The `MATCH` function, on the other hand, has the sole purpose of finding the numeric position of a given item in a list. How’s this? It’s simple.

Given the previous example’s information, let’s find out Liza’s position.

Using this `MATCH` function, it will yield to the value of 5.

To interpret the value, it would merely mean that Liza is the 5th student.

We wanted to see Liza’s position in the class. Therefore, we typed in her name. Then, we will select the list where we want to get our data. In this case, since we are looking for Liza (first name basis), we chose the range cell range A2:A11. Ultimately, we added the value, ‘0‘, which means we want to obtain the result of an exact match.

Contrary to just using `0`, you also have the options `1` (default, if not provided ‘0’) and `-1` where the former and the latter both work when we’re dealing a sorted range of data, and we wish to return either the largest value or equal to the `search_key` or smallest value or equal to the `search_key`.

We will get into this more when we explore the anatomy of both the `INDEX` and `MATCH` functions.

The Anatomy of the INDEX and MATCH Function

The way we write the INDEX and MATCH together in Google Sheets is by nesting one inside the other. Here’s how it will look like:

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

Let’s break this function down and understand it:

• `=` every function in the Google Sheet starts with an equal sign.
• `INDEX` function retrieves a value from a specific range.
• `reference` is the range where you want to get your data.
• `MATCH` function gives the position of your search-key.
• `search_key` is the item that you want to find out.
• `range` from your `MATCH` function is the row/column where your `search_key` is.
• `search_type` controls whether or not you’re searching for an exact value which we indicate by `0` or an approximate one which we indicate by either using a `1` or a `-1`.

⚠ A Few Notes When Using the MATCH Function

• Remember that when you add a text, enclose it in a quote-unquote symbol “”.
• The `MATCH` function isn’t case-sensitive. Whether you type it in uppercase or lowercase, it doesn’t matter.
• If you’re looking into an approximate match, then do not use “0“. Here are the other match types (or otherwise known as `search_type`) that you can look into:

Approximations are also used, especially when you are trying to find out the best match in a set of values.

⚠ A Few More Reminders to Make Your INDEX and MATCH Function Work Perfectly

1. Always start your formula with the function `INDEX`.
2. When you open a parenthesis ‘(‘, make sure to close it ‘). If you fail to close one, expect that you will not get your desired outcome.
3. You can use more that one `MATCH`. Just be sure to close every criterion.
4. If you’re confused about something, then simply go back to the basics: `INDEX` yields a specific value. `MATCH` finds the position.
5. `MATCH` isn’t case sensitive, as we talked about above.
6. Remember when to use the right match types (`search_type`). If you’re aiming for the same result as what we had in our example, then `search_type` will need to be `0`.

A Real Example of Using INDEX and MATCH Function

Let’s focus on the example below to see how INDEX and MATCH Function are used in Google Sheets.

As shown above, we are given a list of 10 salespeople and their corresponding sales in the last three months of 2019. Our goal is to know how much was produced by a salesperson in a specific month.

Let’s see what we did to achieve this goal:

• We labeled the cells accordingly: Name, Month, and Sales. Beside it, it will be its respective value. Therefore, we will keep it empty at this step.
• Next, we worked on the formula at cell F5.
• We started by writing the function, `INDEX` after an equal sign `=`.
• We selected the cell range B2:C11. This range is the sales values of November and December.
• We then added a `MATCH` function and selected F3, an empty cell where we can type in any name of a salesperson. In this example, we have given the name of John.
• Next, we selected the range A2:A11, where the names of the salespersons are located. The `MATCH` function will try and match the name John with the selected range.
• Then, we added `0` because we wanted an exact match.
• Moving on to the second set of the `MATCH` function, we selected F4. This is a blank cell where we can type in a specific month (November/December). In the example above, we have stated November since we want to obtain the sales record for John for November.
• We selected the range B1:C1, where both the November and December months are placed. Based on what we entered in F4, be it “November” or “December,” this cell range will be used to identify which column to look through.
• Afterwhich, we typed `0` because we wanted an exact match.
• Ultimately, we closed the formula with a close parenthesis ‘).’

Hopefully, the above can clear any confusion or doubt you may have had.

You may make a copy of the spreadsheet using the link I have attached below:

Have a feel on how to work with this formula. Try it out for yourself. Once you’ve understood it, let’s jump right into writing and using the INDEX and MATCH together in Google Sheets.

How to Use the INDEX and MATCH Together in Google Sheets

1. First and foremost, you will want to choose a cell where you want to create the formula. For this guide, I will create the formula in the cell F5.

1. Now, as we discussed previously, we begin any function in Google Sheets using an equal sign ‘=.’ So enter the ‘=‘ sign and then follow it up with the `INDEX` and an opening parenthesis ‘(‘ to begin the function.

1. You will now need to select the cell range where all your data is. In our case, all the sales values are occupied in the cells B2:C11. This is the range where we want to get the data.

1. Add comma ‘,‘ to separate as we enter our next attribute for our function.

1. It’s time to write our `MATCH` function! Firstly, type in `MATCH`, and an open parenthesis ‘(‘. Then, select the cell, F3. In this cell, we will input the name of a specific salesperson of whom we want to obtain the sales value.

1. Next, select the cells A2:A11. These are the names of the salespersons. Since we’ve provided the name of the salesperson back in Step 5, the cell range here is to allow the `MATCH` function to go through the list and “match” the name we gave to the names in the list. Once you’ve added the cell range, separate again with a comma ‘,‘, then input `0` because we want an exact match. Afterwhich, close the parenthesis, then another comma.

1. We add another `MATCH` along with an opening parenthesis ‘(. ‘

1. Now, for this `MATCH` function, we will select the cell, F4. Why F4? It’s because, in this cell, we will input the month which we’re trying to locate. For this tutorial, I’ll be adding November.

1. Again, separate with a comma, then select the range B1:C1, or the months November and December. For instance, if there were to be three months of November, December, January with January being D1, then the cell range will be B1:D1.

1. Lastly, type in “0” as we want an exact match. Then hit on the Enter key to obtain the result. Voila!

Your formula in the cell F5 should look like this:

Test out this formula by typing in another name and changing the month. See the difference!

At first, it may seem complicated, but when you’re used to the `INDEX` and `MATCH` function, you will surely love its magic. Again, we recommend that you make a copy of the spreadsheet and try playing around with it, tweaking it and testing how the function works. 🙂

That’s it. Well done! 👏🏆 You can now use the `INDEX` and `MATCH` together in Google Sheets along with the other numerous Google Sheets formulas to create even more powerful formulas that can make your life much more comfortable. 🙂

Get emails from us about Google Sheets.

Our goal this year is to create lots of rich, bite-sized tutorials for Google Sheets users like you. If you liked this one, you'll love what we are working on! Readers receive ✨ early access ✨ to new content.

Read More

How To Use ATAN Function in Google Sheets

The ATAN function in Google Sheets is useful when you need to return the inverse tangent of a…
Read More

How to Find Unique Values in Google Sheets

Finding unique values in Google Sheets is useful to clean up huge amounts of data. This is to…
Read More

How to use INDEX and MATCH Function with Multiple Criteria in Google Sheets

The INDEX and MATCH Function with Multiple Criteria in Google Sheets is useful if you want to look…
Read More

How to Use the IMSEC Function in Google Sheets

This guide will explain how to use the IMSEC function in Google Sheets. Complex numbers are created by…
Read More

How to Transpose Data in Google Sheets

Knowing how to transpose data in Google Sheets is useful if you want to switch the rows and…
Read More

How to Use RANK.EQ Function in Google Sheets

The RANK.EQ function in Google Sheets is used to determine the rank of a specific value in a…