The INDEX and MATCH together in Google Sheets come useful if you want to perform lookups.
Table of Contents
MATCH together in Google Sheets performs similarly to like
VLOOKUP, but even better (which we will get into later in the post below). This function requires two functions which are the
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:
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. 🙂
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.
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
We will get into this more when we explore the anatomy of both the
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.
INDEXfunction retrieves a value from a specific range.
referenceis the range where you want to get your data.
MATCHfunction gives the position of your search-key.
search_keyis the item that you want to find out.
MATCHfunction is the row/column where your
search_typecontrols whether or not you’re searching for an exact value which we indicate by
0or an approximate one which we indicate by either using a
⚠ A Few Notes When Using the MATCH Function
- Remember that when you add a text, enclose it in a quote-unquote symbol “”.
MATCHfunction 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
- Always start your formula with the function
- 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.
- You can use more that one
MATCH. Just be sure to close every criterion.
- If you’re confused about something, then simply go back to the basics:
INDEXyields a specific value.
MATCHfinds the position.
MATCHisn’t case sensitive, as we talked about above.
- 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_typewill need to be
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,
INDEXafter an equal sign
- We selected the cell range B2:C11. This range is the sales values of November and December.
- We then added a
MATCHfunction 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
MATCHfunction will try and match the name John with the selected range.
- Then, we added
0because we wanted an exact match.
- Moving on to the second set of the
MATCHfunction, 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
0because 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
- 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.
- 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
INDEXand an opening parenthesis ‘(‘ to begin the function.
- 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.
- Add comma ‘,‘ to separate as we enter our next attribute for our function.
- It’s time to write our
MATCHfunction! 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.
- 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
MATCHfunction 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
0because we want an exact match. Afterwhich, close the parenthesis, then another comma.
- We add another
MATCHalong with an opening parenthesis ‘(. ‘
- Now, for this
MATCHfunction, 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.
- 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.
- 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
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
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. 🙂