How To Use INDEX and MATCH Together in Google Sheets

INDEX and MATCH Together in Google Sheets
How To Use INDEX and MATCH Together in Google Sheets – Sheetaki

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)

 

INDEX and MATCH Function in Google Sheets

 

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.

INDEX and MATCH Function in Google Sheets

 

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

INDEX and MATCH Function in Google Sheets

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:

INDEX and MATCH Function in Google Sheets

 

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.

INDEX and MATCH Function 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.

INDEX and MATCH Function in Google Sheets

 

  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.

INDEX and MATCH Function in Google Sheets

 

  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.

INDEX and MATCH Function in Google Sheets

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

INDEX and MATCH Function in Google Sheets

  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.

INDEX and MATCH Function in Google Sheets

  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.

INDEX and MATCH Function in Google Sheets

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

INDEX and MATCH Function in Google Sheets

  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.

INDEX and MATCH Function in Google Sheets

  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.

INDEX and MATCH Function in Google Sheets

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

INDEX and MATCH Function in Google Sheets

Your formula in the cell F5 should look like this:

INDEX and MATCH Function in Google Sheets

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

 

Don’t forget to share this post!

0 Shares:
Leave a Reply

Your email address will not be published. Required fields are marked *

You May Also Like