How to Use INDEX MATCH with Multiple Criteria in Google Sheets

INDEX MATCH is a powerful combination of functions in Google Sheets that allows you to look up a value in a table based on multiple criteria. If you're familiar with VLOOKUP or HLOOKUP, INDEX MATCH is an alternative that can handle more complex lookups.

Here's how to use INDEX MATCH with multiple criteria in Google Sheets:

1. First, let's understand the syntax of INDEX and MATCH functions individually:INDEX(reference, [row], [column])
   • reference: The range of cells to search.
   • row: The row number in the reference range.
   • column: The column number in the reference range.

   MATCH(search_key, range, [search_type])

   • search_key: The value to look up.
   • range: The range of cells to search.
   • search_type: The type of match to perform, 1 for less than, 0 for exact match, and -1 for greater than. (Optional, default is 1)
2. To use INDEX MATCH with multiple criteria, we need to combine the MATCH function with an ARRAYFORMULA. The syntax will look like this:INDEX(range, MATCH(1, ARRAYFORMULA((criteria1_range=criteria1)*(criteria2_range=criteria2)), 0), result_column)
   • range: The range of cells containing the data.
   • criteria1_range: The range of cells containing the first criteria.
   • criteria1: The first criteria to match.
   • criteria2_range: The range of cells containing the second criteria.
   • criteria2: The second criteria to match.
   • result_column: The column number in the range where the result is located.
3. You can also add more criteria by extending the ARRAYFORMULA:INDEX(range, MATCH(1, ARRAYFORMULA((criteria1_range=criteria1)(criteria2_range=criteria2)(criteria3_range=criteria3)), 0), result_column)

Example

Let's say we have a table with data about the sales of different products on different days:

    A           B       C
1   Product     Date    Sales
2   Apples      1-Jan   100
3   Apples      2-Jan   120
4   Oranges     1-Jan   80
5   Oranges     2-Jan   90

We want to find the sales of Apples on 2-Jan. Here's how to use INDEX MATCH with multiple criteria:

1. In a new cell, type the following formula:

   =INDEX(A1:C5, MATCH(1, ARRAYFORMULA((A2:A5="Apples")*(B2:B5="2-Jan")), 0), 3)

2. Press Enter. The formula will return the sales value of 120, which is the sales of Apples on 2-Jan.