If you want to create a mini search function that displays a value in a matrix based on two identifying pieces of information, then this article is for you!
Finding the Value in a Matrix
The idea is to set up a formula that will match one piece of information to a cell in the first column, another piece of information to a cell in the first row, and then retrieve the value of the cell where they intersect. For example, if you want to get the quantity of 5 lb flour sold on July 17th, you need to do the following: find the column for July 17th and the row for 5 lb flour, then find the corresponding cell based on these two row and column numbers.
The answer is 126 pieces.
The INDEX MATCH Method
You can achieve the same thing using the INDEX MATCH method. In fact, 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 combine them to easily search for a value in a matrix.
Note that the formulas presented here will also work in Excel.
How to Use the MATCH Function in Google Sheets
The MATCH function returns the location of the cell in a range that exactly matches the given string in the same row or column. Its syntax is as follows:
=MATCH(lookup_value, range, [search_type])
The lookup_value is the value you are searching for in the range. The search_type tells the MATCH function whether the range is sorted or not. You need to set it to 0 if the range is not sorted alphabetically, -1 if the data is sorted in descending order (returns the smallest value greater than or equal to the lookup_value), or 1 if the data is sorted in ascending order (returns the largest value less than or equal to the lookup_value). MATCH returns a numeric value representing the location of the cell along the column or row.
MATCH can be used to search through a column or a row. This feature is important and will be useful when we combine it with the INDEX function.
To show you how it works, we will apply it to our example data. We want to search through the left column to find an exact match with the string “16 oz butter”.
=MATCH("16 oz butter", A2:A12, 0)
We can also store the string in a cell and reference it in the MATCH formula. The formula would then be:
=MATCH(K2, A2:A12, 0)
How to Use the INDEX Function
The INDEX function returns the contents of a cell at specific coordinates within a range. The coordinates indicate the relative position of the cell in the specified range, where (1,1) is the top-left cell of the selected range. Although this cell is often A1, it is not mandatory. Here is the syntax of the function:
=INDEX(reference, [row], [column])
The reference is the range of cells that will be searched based on the given row and column number. Here is a diagram showing the coordinates for the INDEX function:
Let’s revisit the same example as before. We want to use the INDEX function to select 84. The specified cell, C4, is located at row 3 and column 2:
=INDEX(B2:H12, 3, 2)
You don’t need to enclose the coordinates in double quotes. The result is:
You can also reference cells containing the relative position in the formula:
=INDEX(B2:H12, K2, K3)
The result is:
INDEX MATCH: How to Use INDEX MATCH in Google Sheets
We can finally combine MATCH and INDEX together. INDEX requires two arguments to locate the cell, one for the column and one for the row. Both will be provided by the MATCH function assigned to the columns and rows.
Here is the formula used to combine INDEX and MATCH:
=INDEX(data_range, MATCH(cell_with_row_label, row_label_range, 0), MATCH(cell_with_column_label, column_label_range, 0))
Where:
- data_range is the range where the data is located
- cell_with_row_label is the cell where you can type the label you are searching for in the row
- row_label_range is the range where the row labels are listed
- cell_with_column_label is the cell where you can type the label you are searching for in the column
- column_label_range is the range where the column labels are listed
For our example, the row and column labels are clearly identified:
So we set 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 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 cool, right?
INDEX MATCH vs. the VLOOKUP Function
Compared to VLOOKUP, INDEX MATCH is much more flexible, giving you greater freedom and accuracy. MATCH returns a numeric value representing a cell in a row or column that exactly matches the lookup, and the INDEX function returns the string present in a certain cell of the selected range. Together, INDEX and MATCH create a mini search bar for your spreadsheet with major advantages over VLOOKUP.
Accuracy
INDEX MATCH performs 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 in uppercase.
Flexibility
INDEX MATCH will automatically adjust to added or moved rows and columns. This great advantage allows users to not have to worry about breaking their formulas every time they move their data.
Completeness
While VLOOKUP only searches to the right of the lookup cell, INDEX MATCH will also search to the left of the lookup cell. This not only allows for more comprehensive searching, but it also means that your formula cell does not need to be to the left of the data, giving you even more freedom in how you organize your spreadsheet.
Change the Way You Use Google Sheets
Google Sheets is a powerful tool for organizing and analyzing your data. By using INDEX MATCH, you can fully utilize its features and get accurate and flexible results.
To delve even deeper into learning Google Sheets, visit Crawlan.com. There, you will find helpful resources, tutorials, and tips to unleash the full potential of Google Sheets and improve your productivity.
And there you have it, now you’re ready to become true Google Sheets experts!
