Let’s say you want to make a mini-search function that will display a value from a matrix given two pieces of identifying information:
What you need to do is to implement a formula that helps you match one piece of information to a cell in the first column, the other piece of information to a cell in the first row, and then retrieve the value of the cell where they overlap. For example, if we want to get the amount of 5-lb flour sold on July 17, we do the following: We find the column for July 17 and the row for 5-lb flour, then find the cell with those row and column numbers.
The answer is 126 pieces.
We can do the same thing using the INDEX MATCH method. Actually, this trick is a combination of the INDEX and MATCH functions. In this tutorial, we will explore how MATCH and INDEX work individually and then use INDEX and MATCH together to easily search for a value in the matrix.
Note that the formulas here will also work in Excel.
MATCH returns the location of the cell in a range that is an exact match with given string in the same row or column. Its syntax is:
=MATCH(search_key, range, [search_type])
The search_key is the value that you are looking for in the range. The search_type indicates to the MATCH function whether the range is sorted or not. You need to set it to 0 if the range is not sorted alphanumerically, -1 if the data is sorted in descending order (will return smallest value greater than or equal to search_key), or 1 if the data is sorted in ascending order (will return greatest value less than or equal to search_key). MATCH returns a number value representing the location of the cell along the column or row.
MATCH can be used to scan down a column or across a row. This important feature of MATCH will be useful later when we combine it with the INDEX function.
To briefly demonstrate how this works, let us test it in our sample data. We want to scan the left column for an exact match for the string "16 oz butter."
We use the following formula:
=MATCH("16 oz butter",A2:A12,0)
Where the string is enclosed in quotation marks. Here is the result:
We can also store the string in a cell and point to it in the MATCH formula. The formula will now be:
=MATCH(K2,A2:A12,0)
And the result is:
See our article on the google sheets match function for a more detailed breakdown.
The INDEX function returns the contents of a cell at specific coordinates within a range. The coordinates point to the relative position of the cell within the specified range, where (1,1) is the upper-leftmost cell of the selected range. Although this cell is often A1, the upper left-most cell of your selected range does not have to be the upper-leftmost cell of your spreadsheet. To further understand this, here is the syntax of the function:
=INDEX(reference, [row], [column])
Where reference is the range of cells that will be scanned for a given set of row number and column number. Below is a diagram showing the set of coordinates for the INDEX function:
Let us use the same example as before. We want to use INDEX function to select 84. This specified cell, C4, is in row 3 and column 2:
The formula is
=INDEX(B2:H12,3,2)
The coordinates do not need to be enclosed in double quotation marks. The result is:
You can also point the formula to cells containing the relative position:
=INDEX(B2:H12,K2,K3)
The result is:
We can now finally combine MATCH and INDEX together. INDEX requires two arguments to locate the cell, one for the column and the other one for the row. Both of them will be provided by the MATCH function assigned to the columns and rows.
indexmatch uses the following formula:
=INDEX(range_of_data,MATCH(cell_containing_row,range_of_row_labels,0),
MATCH(cell_containing_column,range_of_column_labels,0))
Where:
range_of_data is the range where the data is located
cell_containing_row is the cell where we can type the label we are looking for in the row
range_of_row_labels is the range where the row labels are listed
cell_containing_column is the cell where we can type the label we are looking for in the row
range_of_column_labels is the range where the row labels are listed
For our example, the row labels and column labels are clearly identified:
So we are defining the range as B2:H12, the cells where the values are located. The row label range is A2:A12 while the column label range is B1:H1. We then allocate a cell for the row label and another one for the column label. Here is the formula:
=INDEX(B2:H12,MATCH(K2,A2:A12,0),MATCH(K3,B1:H1,0))
And here is the result:
Pretty neat, eh?
Compared to VLOOKUP, INDEXMATCH is much more flexible, giving you more freedom and better accuracy. MATCH returns a number value representing a cell in a row or column with an exact match of the search query, and the INDEX function returns the string present in a certain cell within the selected range. Together, INDEX and MATCH creates a mini-search bar for your spreadsheet with major advantages over VLOOKUP.
Indexmatch conducts a case sensitive search, while VLOOKUP does not use case-sensitive search. This feature allows users to search for more specific targets that may be capitalized.
Indexmatch will automatically adjust to added rows and columns and/or moved data. This major advantage frees users from having to worry about messing up their formulas every time they shift their data.
While VLOOKUP only searches to the right of the search cell, INDEXMATCH will search to the left of the search cell. Not only does this provide a more comprehensive search, but it means that your formula cell does not have to come to the left of the data, giving you even more freedom with how you arrange your spreadsheet.
