What it does – Dynamic two-way lookups based on both rows and columns.
Syntax=INDEX(reference, MATCH(search_key, range, search_type), [column])reference – The range of cells from which to retrieve the data.search_key, range, search_type – The value to search for, the range of cells in which to look for the value, and the manner in which to match the range, respectively. The MATCH function is used in place of the row parameter of the INDEX function to make the row dynamic.column – The index of the column. It takes a default value of 0.
Sample Usage=INDEX (B2:E10, MATCH("John", A2:A10, 0), 2)
//This returns the corresponding value for John from the range B2:E10 from the second column.
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.
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.
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.
Example Data
The data shown below shows a list of some students studying in a college along with their IDs, departments, and marks.
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.
- 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.
- 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
- Hlookup in Google Sheets: Learn how to perform horizontal lookup in Google Sheets.
- Vlookup in Google Sheets: An easy guide on how to perform vertical lookup in Google Sheets.