Index Match in Google Sheets 💻 – Formula, Examples (2022)

Index Match in Google Sheets 💻 – Formula, Examples (2022)
Reading Time: 5 minutes
How to Use Index-Match Functions in Google Sheets

Learn how to use Index Match in Google Sheets.

Why use Index Match in Google Sheets

As the name suggests Index Match in Google Sheets is a combination of two functions, INDEX and MATCH. Index Match in Google Sheets is very useful when you want to perform advanced lookups on your data and provides greater flexibility than other search/lookup functions by allowing you to perform dynamic two-way lookups based on both rows and columns.

In this guide, you will learn how to

✅ Use only Index function in Google Sheets

✅ Use only Match function in Google Sheets

✅ Use both Index Match in Google Sheets

Index Match formula can be a great alternative for Vlookup and Hlookup in Google Sheets.

MATCH Function

Given a range of cells and a value in that range, this function returns the relative position of the specified value in that range of cells. 

Syntax:-

MATCH(search_key, range, search_type)

search_key: refers to the value for which we want to find the relative position.  

range: the range of cells in which we want to search for the specified value. 

search_type (optional):   specifies the manner in which matching will take place. This argument can take three values- 0, 1, -1.

  • 0: indicates that the search should be made for an exactly matching value to the search_key and is usually used when the range is not sorted. 
  • 1: This is the default value. In this case, the range is already sorted in ascending order, and the largest value less than or equal to the search_key is returned. 
  • -1: Here the range is already sorted in descending order and the smallest value greater than or equal to the search_key is returned.

Example:- 

Following is a list of 5 students in a class.

List of students in a class
Fig 1: List of students in a class | Example – Index Match in Google Sheets

Suppose you want to find the position of student Robert in the list. You can easily do this by using the MATCH function as follows-

=MATCH(“Robert”, B2:B6,0)

As you can see, the function returns position 3 as output.

Output returned by the MATCH function in Google Sheets
Fig 2: Output returned by the MATCH function | Example – Index Match in Google Sheets

INDEX Function

Given a range of cells, row index, and column index, this function returns the contents of the cell at the intersection of the specified row and column. 

Syntax:-

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

  • reference: range of cells from which we want to extract the desired cell content.
  • [row] (optional):  specifies the index of the row. It takes a default value of 0. 
  • [column] (optional):  specifies the index of the column. It takes a default value of 0. 

Example:-

From the same example as above, let us now try to fetch the name of the student at position 4 in the list. We can do this by using the INDEX function as follows-

=INDEX(B2:B6, 4, 1)

The function returns the name of the student at position 4 in the list, that is Stacy. 

Output returned by INDEX function in Google Sheets
Fig 3: Output returned by INDEX function | Example – Index Match in Google Sheets

Example Data

The data shown below shows a list of some students studying in a college along with their IDs, departments, and marks. 

Table in Google Sheets showing a list of some students along with their IDs, departments, and marks. 
Fig 4: Example Data for Index Match in Google Sheets

Now, let’s have a look at how to use index match with the help of some examples. 

Combining Google Sheets Index Match when the search uses-

Single criteria 

Now we want to find the marks of any student in the class. As you can see in the smaller table shown in the figure below, I have considered the example of a student named Cassie here. In case we need to find the marks of any other student, the student name entry in the smaller table will change. Therefore we can’t hardcode it. Hence this becomes an apt situation to use Index Match. 

So we type in the following formula to calculate the corresponding marks.

=INDEX(D2:D11,MATCH(E4,B2:B11,0))

The formula returns the marks corresponding to the student Cassie.

Using Index Match with single criteria
Fig 5: Using Index Match in Google Sheets with single criteria
  • Formula explanation:-

First, let’s have a look at the inner MATCH function. 

MATCH(E4,B2:B11,0)

This function searches for the value in cell E4 in the range B2:B11 and returns its position. Since Cassie is at position 6 in the list, the function returns a value of 6. 

INDEX(D2:D11,MATCH(E4,B2:B11,0))

The output of MATCH function,6, becomes an argument for the INDEX function. The function searches for the value at position 6 in the cell range D2:D11 and returns it as the output. Therefore 89 is the final output as it is present at the 6th position in the specified cell range. 

Similarly to calculate the marks of other students, just keep changing the student name entry in cell E4 and the formula calculates the corresponding marks. 

Multiple criteria

In this case, let us suppose that instead of marks we now want to find the department of any student. This means the entry in cell F3 can also change and is dynamic. Hence here we introduce a second MATCH function in the formula which becomes the third parameter of the INDEX function. 

The formula which we use in this case is as follows- 

=INDEX(A2:D11,MATCH(E4,B2:B11,0),MATCH(F3,A1:D1,0))

The following output is displayed. 

Using Index Match with multiple criteria  in Google Sheets
Fig 6: Using Google Sheets Index Match with multiple criteria
  • Formula explanation:-

Let’s start with the first MATCH function. 

This function works similar to the MATCH function in the above example and returns 8 as the output. This is Patricia’s position in the cell range B2:B11. 

Moving on to the second MATCH function,

MATCH(F3,A1:D1,0)

This function searches for the value in cell F3 in all the columns from A1 to D1 and returns its corresponding position. In this case, the output returned is 3, since Department is the third column. 

The final INDEX function is shown below. 

=INDEX(A2:D11,MATCH(E4,B2:B11,0),MATCH(F3,A1:D1,0))

Now the INDEX function looks for the 8th row and 3rd column in the cell range A2:D11 and returns the contents of the cell present at the intersection of the specified row and column. 

So by using this version of index match we can change the name of the student as well as vary the information we want to access for that student. This gives us greater flexibility and is more convenient. 

See Also

2 responses to “Index Match in Google Sheets 💻 – Formula, Examples (2022)”

Leave a Reply