How To Use INDEX MATCH Function Google Sheets with Examples

Do you ever find looking up specific data in a huge table challenging?

Spreadsheet programs like Google Sheets boast powerful functions such as the VLOOKUP to extract specific text or numbers from a huge dataset. However, those lookup functions have a few limitations.

Another alternative to searching a text string or number across the table is to combine the INDEX and MATCH functions. This combination can work wonders, allowing dynamic searches across rows and columns.

It is used to create a mini search bar in Google Sheets.

This is the ultimate guide to using the INDEX MATCH function Google Sheets. We will discuss individual functions and learn how the combination is used to find a specific data set.

Make sure to read the article to the end so you do not miss any crucial information that will help you master the INDEX and MATCH functions in Google Sheets.

When to Use the INDEX and MATCH Functions in Google Sheets

The INDEX and MATCH functions together in Google Sheets are generally used for lookup and reference purposes.

Below are a few situations where you can use them instead of VLOOKUP without giving a second thought.

• Non-Contiguous Lookup – There are situations where we need to retrieve a value from a data range based on matching criteria that are not located in the same row or column. Because VLOOKUP requires contiguous data, you can use the INDEX and MATCH functions to fulfill this purpose.
• Flexibility – The INDEX and MATCH functions are very flexible. You can perform complex lookups vertically and horizontally and match based on multiple criteria, unlike the VLOOKUP function, which can match only a single criteria.
• Dynamic Range – If you have a table where the data range may change frequently, the INDEX and MATCH functions can more easily adapt to these changes, as they do not rely on static references of rows and columns like the VLOOKUP function.
• Avoiding Issues with Sorted Data – The INDEX and MATCH functions don’t require the data to be sorted, which is one limitation of the VLOOKUP function.

These were some scenarios where the INDEX and MATCH functions stand out and are helpful. 

Let’s look at the functions we will use in the article.

Download the Example Google Sheet

Click on the link below to download the Google Sheet containing the datasets we used in the following sections.

Click Here To Make A Copy!

Follow me through this article and practice the things we are about to discuss. Note that both functions are complex and require practice to master them. 

If your sheet has the required data, you can skip downloading the example sheet.

Explained: The INDEX Function in Google Sheets

The INDEX function is a fundamental function that returns the value of a specific cell based on the row and column numbers. 

Before using the function, it is first necessary to know its syntax. Here’s the general syntax of the INDEX function,

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

Each argument of the above function needs to be replaced with proper data,

• Where [reference] is the set of data (data range) that will be searched through the table based on specific row and column numbers
• [row] is the row number in the data range from which you want to retrieve the data
• [column] is the column number in the range from which you want to pull the data

Both the second and third arguments are optional. 

Let’s understand the INDEX function with an example.

Below is the screenshot of the dataset which we will be using to illustrate the process of the INDEX function.

Task: To find the Annual Salary of ‘David Miller’ using the INDEX function 

Steps to be followed:

• Select the cell “H4“
• Type “=INDEX”
• Select the first option from the popup or press the “Tab” key
• Replace the reference argument with the data range you wish to be scanned
  (The data range referred to here is “A2:F11“)
• Press “,” to move to the next argument
• In the next argument, enter the row number of the required cell
  (Since we are looking for the Salary of David Miller, the row number would be 4)
• Press “,” to move to the next argument
• Replace the column argument with the column number that corresponds to the desired cell
  (The column number of the Salary would be 5)
• Complete the formula with a closing parenthesis “)“
• Press “Enter” on your keyboard
  

So, the final formula would be as follows,

=INDEX(A2:E11,4,5)

This will return the Salary of David Miller.

You might have noticed that we must manually enter the row and column number. Surely, it is time-consuming and may lead to an error.

So, is there any way to retrieve values based on different criteria or keys?

The good news is, Yes. You can combine the INDEX function with the MATCH function to perform lookups based on different criteria by not relying on the row and column numbers.

Explained: The MATCH Function in Google Sheets

The MATCH function in Google Sheets returns the position of a specific key in the specified data range. Unlike the INDEX function, you don’t need to enter the row and column numbers for an exact match.

The General Syntax of the MATCH function is 

=MATCH( search_key,range,[search_type])

Here’s the explanation for each argument of the MATCH function,

• The [search_key] is the string or the reference cell you are looking for
• The [range] is the range of cells (one-dimensional array) that is to be searched for the key
• [search_type] indicates whether the range is sorted or not. If the range is alphanumerically sorted, you need to set it to ‘0’. If sorted in descending order, it should be set to ‘-1’ and return the smallest value greater than or equal to the search_key. If the array is sorted in ascending order, you need to set the argument to ‘1’, and it will return the greatest value less than or equal to the search_key.

Let’s consider the same example above to understand the MATCH function.

Task: To find the position of ‘Steve Smith’ using the MATCH function

Steps to be followed:

• Select the cell “H8“
• Type “=MATCH”
• Select the first option from the popup or press the “Tab” key
• Replace the search_key argument with the string or the cell reference in which the string has been stored. Here, the search_key would be “Steve Smith”, but since we have stored it in cell G8, we will enter “G8” here
• Press “,” to move to the next argument
• For the second argument of the MATCH function, enter the range of data that is to be scanned for the specific string mentioned in the search_key
  (The data range referred to here is “A2:A1“)
• Press “,” to move to the next argument
• Replace the search_type argument with “0“
• Complete the formula with a closing parenthesis “)“
• Press the “Enter” on your keyboard
  

Our final formula should look like below,

=MATCH(G8,A2:A11,0)

If you wish to search by string or exact name “Steve Smith”, then the formula will be as follows,

=MATCH("Steve Smith",A2:A11,0)

Make sure you enter the string in double quotation marks; otherwise, the formula will return an error.

This will return the “Steve Smith” position by searching for it in the cell range A2:A11.

Using the dropdown, you can take the functionality of this combination to the next level. It allows you to automatically return each employee’s details when they are entered in the dropdown.

How to Use The INDEX MATCH Functions to Create A Mini Search Bar in Google Sheets

You can combine the INDEX and MATCH functions in Google Sheets to return the value of a cell within a given range based on specified criteria

Here’s how it works,

• The INDEX function will return the value
• The MATCH function will provide the row and column numbers

The syntax of the INDEX and MATCH functions will be as follows,

=INDEX(range_of_data,MATCH(lookup_value,range_to_search,0))

• Where [range_of_data] is the data range where the data is located
• [lookup_value] is the value you want to search for within the specified range
• [range_to_search] is the range of cells or arrays where you wish to search for the lookup value
• [0] is the parameter that specifies an exact match.

To understand it better, consider the example used in the sections above. Refer to the following image.

Task: To find the Annual Salary of Henry Andrews.

Let’s begin,

• Select the cell “H12“
• Type “=INDEX”
• Select the first option from the popup or press the “Tab” key
• Replace the range_of_data argument with the entire dataset range. Here, the dataset range will be “A2:E11“
• Press “,” to move to the next argument
• Type in “MATCH“
  
  (As the MATCH is a built-in function in Google Sheets, you will see a popup as shown in the above image)
• Select the first option or press the “Tab” key
• Replace the lookup_value with the string or cell reference in which the string has been stored. Here, the lookup value is “Henry Andrews” or if you have stored the string in a cell, mention the cell reference
• Press “,” to move to the next argument
• In place of the “range_to_search” argument, enter the row range from where you want to retrieve the data
• Press the “,” to move to the next argument
• Finally, enter “0” for an exact match
• For the MATCH function, complete the bracket using the “)“
• Press “,” on your keyboard
• Enter the desired column number to search for the desired criteria. Here, the desired column is the Annual Salary column, so the column number would be “5“
• For the INDEX function, complete the bracket using the “)“
• Press the “Enter” key
  

Our final formula will be as follows,

=INDEX(A2:E11,MATCH(G12,A2:A11,0),5)

Here, we have only used the MATCH function once to pull the row details and simply mentioned the column number instead of using the MATCH function twice.

This formula will return the Annual Salary of “Henry Andrews”.

You can change the employee name in the dropdown present in G12 to display their Annual Salary dynamically.

Overall, the INDEX and MATCH functions combined create a mini search bar for your data in the spreadsheet.

How to Use the INDEX MATCH Function Between Sheets

Using the INDEXMATCH function between sheets is very simple. You need to add the sheet name before specifying the data range or array to be searched for for a particular key.

Let’s consider the following example to understand the INDEXMATCH between two sheets.

Here’s the syntax of the INDEX MATCH function,

=INDEX(Sheet name!range_of_data,MATCH(lookup_value,Sheetname!range_to_search,0)

The sheet name is where the data you want to reference is located. Make sure to enclose the sheet name in single quotations (“) if it contains spaces or special characters.

• [range_of_data] is the data range in the specific sheet from which you want to retrieve the value.
• [lookup_value] is the value you want to search within the specified range.
• [range_to_search] is the range on the specified sheet where you want to search for the lookup value.

Let’s consider the following table, which includes employee details, including their city, age, gender, and annual salary.

We will manually add one more sheet in the same Google Sheet to find

Task: To find the Annual Salary of “Warren Buffet”

Here are the steps:

• Select the cell “G8“
• Type “=INDEX”
• Select the first option from the popup or press the “Tab” key
• Replace the Sheet name with the sheet’s name where your reference data is located. In our case, the sheet name would be “Sheet1” followed by an exclamation mark
• Next, replace the range_of_data argument with the entire dataset range “A2:E11“. It would be scanned in the specified sheet
• Press “,” to move to the next argument
• Type in “MATCH“
  
  (You will see a popup as shown in the above image because the MATCH is a built-in function in Google Sheets)
• Select the first option from the popup or press the “Tab” key
• Replace the lookup_value with the string or cell reference in which the desired string has been stored. Here, the lookup value is “Chris Taylor“, you can also use the cell reference
• Press “,” to move to the next argument
• Enter the Sheet name followed by an exclamation mark
• Now, enter the lookup data range in the range_to_search argument
• Press “,” to move to the next argument
• Type “0” to ensure the exact match
• Now, for the MATCH function, close the bracket using “)“
• Press “,” on your keyboard and enter the column number where the lookup value is to be searched for
  (Here, the Annual Salary column is the desired column. So the column number would be 5) 
• Complete the bracket using “)” for the INDEX function
• Press the “Enter” on your keyboard
  

Our final formula would be as follows,

=INDEX(Sheet1!A2:E11,MATCH(F8,Sheet2!A2:A11,0),5)

Google Sheets will instantly find the data based on the information provided in the INDEX MATCH function. Refer to the above GIF.

Is the INDEXMATCH function better than the VLOOKUP function?

While both of these functions have the same applications, there are a few scenarios in which the INDEX MATCH function dominates the VLOOKUP function.

As we mentioned already, the INDEXMATCH function is more versatile and quicker than the VLOOKUP function.

Here are the reasons why the INDEX MATCH function is better than the VLOOKUP function:

• Horizontal and Vertical Lookup – The INDEXMATCH function can be used for both vertical and horizontal lookups. You can also use the function with multiple criteria. In the case of VLOOKUP, you can define only one criteria for the lookup.
• Left-side Lookup – The VLOOKUP function matches the cells from the columns on the right side of the specified cell. At the same time, the INDEXMATCH function matches the cells from the columns on both sides of the given column.
• Flexibility to add more rows and columns – The VLOOKUP formula may break if you add more rows or columns to the source table. However, this is not the case with the INDEXMATCH function, you can freely add rows and columns as per your preference. The function automatically adjusts its references to accommodate the new changes.
• Text Case – Last but not least, the INDEXMATCH function considers the case-sensitivity while lookup. All you need to do is combine the function with the FIND or EXACT function in Google Sheets.

These are the reasons to choose the INDEXMATCH function over the VLOOKUP. At the beginning of this article, we also discussed particular scenarios for using the INDEXMATCH function.

FAQs: INDEX MATCH Google Sheets

Q. How do I match data in Google Sheets?

The MATCH function in Google Sheets returns the position of a specific key in the specified data range. 

We have already discussed the syntax and working of the MATCH function in Google Sheets above. It is a simple function with a few arguments that are easy to interpret.

Here are the steps to use the MATCH function in Google Sheets, 

• Open the desired Google Sheets
• Double-click on the cell where you wish to insert the MATCH function
• Type “=match“
• Enter the search_key whose position you want to determine
• Press “,” to move to the next argument
• Replace the next argument with the range in which you wish to scan for the specified key
• Press “,” to move to the next argument
• Replace the search_type argument with the “0” for an exact match
• Complete the bracket using the “)”
• Press the “Enter” key

The formula is pretty straightforward. The above section of the article provides more detailed instructions and an example.

Q. Can you use INDEXMATCH instead of VLOOKUP in Google Sheets?

Both INDEXMATCH and VLOOKUP are powerful functions in Google Sheets that can match and return the value from a given cell. 

While the VLOOKUP is more straightforward to use, the INDEXMATCH function requires some knowledge and practice.

But the efforts are worth it, because the INDEXMATCH function is more versatile and flexible when retrieving information from a range.

One main difference between these two functions is that VLOOKUP can recover only static data, while INDEXMATCH functions can return dynamic values.

Q. How do you use INDEXMATCH for multiple criteria in Google Sheets?

If you want to search with the column criteria, start with the lookup range, the data range, and the match type.

On the other hand, in case you wish to use the multiple column criteria, use an ampersand (&) to input the secondary MATCH function.

Q. Is INDEXMATCH faster than VLOOKUP?

Yes, of course. The INDEXMATCH function is quicker than the VLOOKUP function.

Consider that you are working with a larger, complex dataset containing hundreds of rows and columns.

Here, the INDEXMATCH function will work faster than the VLOOKUP because Google will only have to process the lookup and return column rather than the entire array.

To Summarize: The INDEX MATCH Function Google Sheets

We have seen how using INDEXMATCH functions in Google Sheets lets you analyze data through references and lookups.

The INDEX function’s flexibility and the MATCH function’s dynamic searching capabilities make it an ideal tool for efficiently retrieving data based on specific criteria. Compared to traditional methods like VLOOKUP, understanding and using INDEXMATCH offers greater versatility and accuracy in data management tasks.

Feel free to comment below if you are stuck somewhere or have any particular issue while using the INDEX or MATCH or the combination of both functions.

Check out our blog page to learn excellent tips and tricks for becoming a pro at using Google Sheets.