In this tutorial, we will show you exactly how to use the INDEX and MATCH functions with multiple criteria in Google Sheets in just a few simple steps. Simply follow the steps below.
The INDEX function in Google Sheets returns the value of a cell in a specified range based on the row and, optionally, column number. Conversely, the MATCH function locates a value within a range and returns its position. When used together, the MATCH identifies the row or column number, and the INDEX retrieves the corresponding value.
Here's a general syntax for the INDEX and MATCH functions to be used with multiple criteria:
```=INDEX(result_range, MATCH(1, (criteria_range1 = criteria1) * (criteria_range2 = criteria2), 0))```
Where:
result_range is the range from which you want to return a value. This is the column or row that contains the values you're looking for.
1 acts as a constant to indicate the formula should look for rows where all criteria are met (logical "true" results in 1).
criteria_range1 = criteria1 is the first condition you are matching.
criteria_range2 = criteria2 is the second condition you are matching.
* (Multiplication) is an operator that combines the two conditions (logical AND), meaning both criteria must be true for a match.
0 indicates that the MATCH function should find an exact match for both criteria.
Follow the process below to use the INDEX and MATCH syntax with multiple criteria on Google Sheets.
Organize your data into columns, such as "Product Name," "Region," and "Sales." Ensure each column has a clear header and each row contains complete data for that product or region.
Create a separate table where you will enter the search criteria and display the results. For example, reserve cells for "Product" and "Region" in this new table.
Input the criteria you want to search for, such as a specific product name in one cell and a region in another. These will be used to match data from your main table.
In the result table, use the INDEX function to reference the column where the result (e.g., "Sales") will be. An INDEX returns the value from a given row in a specific column.
In our example, the formula we will be using is
```=INDEX(SalesColumn, row_number)```
Since our sales data is in the range C2 to C9, we will input C2:C9 in place of the sales column.
Use the MATCH function to find the row where your criteria match both columns (e.g., "Product" and "Region"). In our example we will be using the formula:
```=INDEX(SalesColumn, MATCH(1, (ProductRange=ProductCriteria)*(RegionRange=RegionCriteria), 0)```
Since our product data is in A2 to A9, we will input A2:A9 in place of product range. Then, the region data is in B2 to B9, so we will use B2:B9 as the region range.
The product and region criteria are where we want our result to be, so we'll put B13 in place of product criteria and B14 for region criteria.
After entering the formula, press Enter. We will now see a value of 200, which corresponds to the sales of "Apple" from region 1.
Test the formula by entering different combinations of criteria and ensuring the correct data is returned. Adjust the ranges or criteria if needed.
We hope that you now have a better understanding of how you can make use of INDEX and MATCH syntax with multiple criteria in Google Sheets. If you enjoyed this article, you might want to check out our tutorial on how to create a leaderboard in Google Sheets or use the XLOOKUP with multiple criteria on Google Sheets.
