Google Sheets INDEX MATCH row and column

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.

Lets 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. Hows this? Its simple.

Given the previous examples information, lets find out Lizas 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 Lizas 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 were 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. Heres how it will look like:

Lets 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.
• referenceis the range where you want to get your data.
• MATCH function gives the position of your search-key.
• search_keyis 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 youre searching for an exact value which we indicate by 0 or an approximate one which we indicate by either using a 1or 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 isnt case-sensitive. Whether you type it in uppercase or lowercase, it doesnt matter.
• If youre 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 youre confused about something, then simply go back to the basics: INDEX yields a specific value. MATCH finds the position.
5. MATCH isnt case sensitive, as we talked about above.
6. Remember when to use the right match types (search_type). If youre 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

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

Lets 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 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 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 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 youve understood it, lets 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. Its 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 weve 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 youve 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? Its because, in this cell, we will input the month which were trying to locate. For this tutorial, Ill 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 youre 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.

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

Subscribe