Imagine being able to search using multiple criteria to find exact matches in your data. That’s exactly what you can do by combining the INDEX and MATCH functions in Google Sheets. In this article, I’ll show you how to use these functions to perform vertical, horizontal, or both types of searches.
Syntax of the INDEX & MATCH Function
Before we dive in, let’s understand the syntax of these two functions as well as the combined syntax:
- INDEX: The INDEX function returns the content of a specific cell using the row and column coordinates.
- MATCH: The MATCH function searches for a specific value in a range and returns its position.
The syntax for the INDEX function is as follows:
INDEX(reference, row, column)The syntax for the MATCH function is as follows:
MATCH(search_key, range, search_type)Combined Syntax for Multiple Criteria Searches
You can use the MATCH function to specify multiple search criteria. Here are the formulas to use based on the type of search:
-
Vertical Search with Multiple Criteria:
INDEX(reference, MATCH(1, (condition1)*(condition2)*...*(conditionN), 0), column) -
Horizontal Search with Multiple Criteria:
INDEX(reference, row, MATCH(1, (condition1)*(condition2)*...*(conditionN), 0)) -
Vertical and Horizontal Search with Multiple Criteria:
INDEX(reference, MATCH(1, (condition1)*(condition2)*...*(conditionN), 0), MATCH(1, (condition1)*(condition2)*...*(conditionN), 0))
Example of Using INDEX & MATCH with Multiple Criteria
Now that you understand the syntax, let’s see how to use these functions to perform searches with multiple criteria:
-
Prepare your criteria: Open Google Sheets and navigate to the spreadsheet where you want to get the results of your search.
-
Use the INDEX & MATCH functions: In this spreadsheet, you can enter your search criteria and use the INDEX & MATCH functions to get the results that match your criteria.
It’s worth noting that you can use these functions to perform vertical, horizontal, or combined searches. Additionally, you can combine them with other functions like AVERAGE to get even more accurate results.
So why not give the INDEX & MATCH functions with multiple criteria a try in Google Sheets? You’ll be amazed at the precision of the results you’ll obtain!
