Don’t forget to share this post

Spreadsheet programs like Microsoft Excel and Google Sheets offer various functions designed to look up values in your tables. Both offer HLOOKUP and VLOOKUP for horizontal and vertical lookups, as well as XLOOKUP, which combines them and provides more flexibility. However, using them with multiple criteria can be very tricky, as it requires various levels of nesting. Fortunately, combining the INDEX & MATCH functions allows you to perform flexible and complex lookups using multiple criteria.

In this guide, you will first review the INDEX and MATCH functions and how to combine them for lookups. You will then learn how a small adjustment allows you to use multiple criteria for your lookups, whether vertical, horizontal, or mixed. You have step-by-step instructions on how to look up values using multiple criteria for a horizontal lookup, a vertical lookup, and one with both horizontal and vertical criteria. Finally, you will learn how to combine INDEX & MATCH with other functions, like AVERAGE.

INDEX & MATCH Function Syntax

Before you get started with lookups based on multiple criteria, it’s a good idea to review the syntax for both functions, as well as the combined syntax.

=INDEX(reference, row*, column*)
  • reference: the range of cells from which you want the values returned.
  • row*: optional. The index of the row to be returned within the reference range of cells. 0 by default. This is where you add MATCH for vertical lookups.
  • column*: The index of the column to be returned within the reference range of cells. 0 by default. This is where you add MATCH for horizontal lookups.
=MATCH(search_key, range, search_type*)
  • search_key: the value you want to search for.
  • range: the one-dimensional array in which to search.
  • search_type*: optional. The manner in which to search.
    • 1 is the default. The function assumes the range is in ascending order and returns the largest value less than or equal to the search key.
    • 0 finds an exact match. The range does not need to be sorted.
    • -1 assumes the range is in descending order and returns the smallest value greater than or equal to the search key.

INDEX & MATCH Combined Syntax

To use the INDEX function, you need to specify the row and column in your range. The function then returns the contents of the cell where the row and column intersect. In other words, you need to provide the coordinates or cell references.

The MATCH function, on the other hand, searches for a specific value and returns the coordinates or cell reference. In fact, it’s exactly what you need to make the INDEX function dynamic. As indicated above, you can replace the row and column arguments in INDEX with MATCH functions.

=INDEX(range,MATCH(search_key,search_column,search_type),MATCH(search_key,search_row,search_type))

Using the formula above, you can specify the row and column criterion for your lookup, but you’re limited to one of each. Fortunately, you can change this by making a small adjustment to the formula.

Combined Syntax for Lookups with Multiple Criteria

Within the MATCH function, you can replace the search_key with “1” and the search_column or search_row with a set of criteria or conditions. Each of these conditions must be within parentheses and joined by a multiplication sign. This forces the function to evaluate each condition and only return “1” if all conditions are met.

Vertical Lookup with Multiple Criteria

Use the following formula for a vertical lookup with multiple criteria.

=INDEX(reference,MATCH(1,(criteria1)*(criteria2)*(criteriaN),0))

Horizontal Lookup with Multiple Criteria

Use the following formula for a horizontal lookup with multiple criteria.

=INDEX(reference,row,MATCH(1,(criteria1)*(criteria2)*(criteriaN),0))

Vertical & Horizontal Lookup with Multiple Criteria

Use the following formula for a lookup with multiple vertical and horizontal criteria.

=INDEX(reference,MATCH(1,(criteria1)*(criteria2)*(criteriaN),0),MATCH(1,(criteria1)*(criteria2)*(criteriaN),0))

How to Use INDEX & MATCH with Multiple Criteria?

Combining the INDEX & MATCH functions allows you to perform lookups by matching values using multiple criteria. In other words, it allows you to disambiguate in cases where there are multiple potential matches for a given value. These lookups can be vertical, horizontal, or both.

Fortunately, both functions are available in Microsoft Excel and Google Sheets. Below, you have instructions on how to do a lookup using two criteria: one vertical and one horizontal. In the next section, you have examples with multiple vertical or horizontal criteria. Finally, you have an example combining INDEX, MATCH, and AVERAGE.

1. Set Up Criteria

  1. 1. Open Google Sheets to the spreadsheet where you want your lookup results. In this case, I want them in the same sheet as the data, but you can use IMPORTRANGE to search in other files.
How to Use INDEX MATCH With Multiple Criteria Open Google Sheets
How to Use INDEX & MATCH With Multiple Criteria - Open Google Sheets
  1. 2. Type in the criteria or the values you want to search for in separate cells. In this case, the first criterion specifies the row, and the second criterion is the column.
How to Use INDEX MATCH With Multiple Criteria Add Criteria
How to Use INDEX & MATCH With Multiple Criteria - Add Criteria

You can easily add drop-down lists in the cells with criteria to make it easier to change them. To learn how to add drop-down lists in Google Sheets, check out this guide on How to Add a Drop-Down List in Google Sheets.

2. INDEX Function

  1. 1. Type the INDEX formula in the cell where you want the lookup result.
How to Use INDEX MATCH With Multiple Criteria INDEX Function
How to Use INDEX & MATCH With Multiple Criteria - INDEX Function
  1. 2. For the first argument, select the range of cells containing the table.
How to Use INDEX MATCH With Multiple Criteria First Argument
How to Use INDEX & MATCH With Multiple Criteria - First Argument

3. MATCH Function - Vertical

  1. 1. For the second INDEX argument, type the MATCH function.
How to Use INDEX MATCH With Multiple Criteria MATCH Function
How to Use INDEX & MATCH With Multiple Criteria - MATCH Function
  1. 2. For the first argument, select the cell with the vertical lookup value.
How to Use INDEX MATCH With Multiple Criteria Search Key
How to Use INDEX & MATCH With Multiple Criteria - Search Key
  1. 3. For the second argument, select the column you want to search.
How to Use INDEX MATCH With Multiple Criteria Search Column
How to Use INDEX & MATCH With Multiple Criteria - Search Column
  1. 4. The third argument should be “0”, as you want an exact match. Close the parenthesis and add a comma.
How to Use INDEX MATCH With Multiple Criteria Exact Match
How to Use INDEX & MATCH With Multiple Criteria - Exact Match

4. MATCH Function - Horizontal

  1. 1. Finally, add another MATCH function. This is the third parameter of the INDEX function, which specifies the column.
How to Use INDEX MATCH With Multiple Criteria 2nd MATCH Function
How to Use INDEX & MATCH With Multiple Criteria - 2nd MATCH Function
  1. 2. Select the cell with the horizontal lookup value as the first argument.
How to Use INDEX MATCH With Multiple Criteria 2nd Search Key
How to Use INDEX & MATCH With Multiple Criteria - 2nd Search Key
  1. 3. Select the row you want to search as the second argument.
How to Use INDEX MATCH With Multiple Criteria Search Row
How to Use INDEX & MATCH With Multiple Criteria - Search Row
  1. 4. Finally, type “0” for the third argument and add two closing parentheses.
How to Use INDEX MATCH With Multiple Criteria Exact Match 2
How to Use INDEX & MATCH With Multiple Criteria - Exact Match
  1. 5. Press ‘Enter’ to see the result.
How to Use INDEX MATCH With Multiple Criteria Result
How to Use INDEX & MATCH With Multiple Criteria - Result
  1. 6. The function will automatically recalculate as soon as you change the criteria.
How to Use INDEX MATCH With Multiple Criteria Change Criteria
How to Use INDEX & MATCH With Multiple Criteria - Change Criteria
How To Share Only One Tab in Google Sheets
How To Share Only One Tab in Google Sheets

When sharing a Google Sheets spreadsheet Google usually tries to share the entire document. Here’s how to share only one tab instead.

READ MORE

INDEX & MATCH with Multiple Criteria: Examples

In this section, you have two examples. For the first example, you will find a match using multiple vertical criteria and one horizontal criterion. For the second example, you will combine INDEX and MATCH with AVERAGE to get the average price of a specific product from a particular vendor.

LOOKUP with Multiple Vertical Criteria and One Horizontal Criterion

  1. 1. Open Google Sheets to the spreadsheet with your data
How to Use INDEX MATCH With Multiple Criteria Open Google Sheets 2
How to Use INDEX & MATCH With Multiple Criteria - Open Google Sheets
  1. 2. Type the criteria into separate cells. I have added drop-down lists to those cells to make it easier to select the criteria.
How to Use INDEX MATCH With Multiple Criteria Add Criteria 2
How to Use INDEX & MATCH With Multiple Criteria - Add Criteria
  1. 3. In the cell where you want the result, type the INDEX function and select the table as the first parameter.
How to Use INDEX MATCH With Multiple Criteria INDEX Range
How to Use INDEX & MATCH With Multiple Criteria - INDEX Range
  1. 4. For the second parameter, add the MATCH function. Add “1” as the search key.
How to Use INDEX MATCH With Multiple Criteria Search Key 2
How to Use INDEX & MATCH With Multiple Criteria - Search Key
  1. 5. After the comma, open the parenthesis and add the criterion or condition. Close the parenthesis and add a multiplication sign.
How to Use INDEX MATCH With Multiple Criteria 1st Vertical Criterion
How to Use INDEX & MATCH With Multiple Criteria - 1st Vertical Criterion
  1. 6. Open another parenthesis to add the second criterion. Once done, close the parenthesis and add a comma.
How to Use INDEX MATCH With Multiple Criteria 2nd Vertical Criterion
How to Use INDEX & MATCH With Multiple Criteria - 2nd Vertical Criterion
  1. 7. Don’t forget to add “0” for an exact match, close the parenthesis, and add another comma.
How to Use INDEX MATCH With Multiple Criteria Exact Match 3
How to Use INDEX & MATCH With Multiple Criteria - Exact Match
  1. 8. Add another MATCH function. Since you only have one horizontal criterion, simply select the search key (month) and the row where it should be matched.
How to Use INDEX MATCH With Multiple Criteria Horizontal Criterion
How to Use INDEX & MATCH With Multiple Criteria - Horizontal Criterion
  1. 9. Finally, type “0” for an exact match and close both sets of parentheses.
How to Use INDEX MATCH With Multiple Criteria Exact Match 4
How to Use INDEX & MATCH With Multiple Criteria - Exact Match
  1. 10. Press ‘Enter’ to see the result.
How to Use INDEX MATCH With Multiple Criteria Lookup Result
How to Use INDEX & MATCH With Multiple Criteria - Lookup Result
How to Password Protect a Google Sheet
How to Password-Protect a Google Sheet?

If you work with important data in Google Sheets, you probably want an extra layer of protection. Here's how you can password protect a Google Sheet

READ MORE

Lookup with Multiple Criteria & AVERAGE

In this example, you will learn how to get the average price for a specific product and vendor over the six months of data you have.

  1. 1. In the spreadsheet where you want the result, type the criteria into separate cells.
How to Use INDEX MATCH With Multiple Criteria Multiple Vertical Criteria
How to Use INDEX & MATCH With Multiple Criteria - Multiple Vertical Criteria
  1. 2. In the cell where you want the result, type the AVERAGE function.
How to Use INDEX MATCH With Multiple Criteria AVERAGE Function
How to Use INDEX & MATCH With Multiple Criteria - AVERAGE Function
  1. 3. Next, type the INDEX function.
How to Use INDEX MATCH With Multiple Criteria INDEX Function 2
How to Use INDEX & MATCH With Multiple Criteria - INDEX Function
  1. 4. Select the table as the first parameter for the INDEX function.
How to Use INDEX MATCH With Multiple Criteria Select Table
How to Use INDEX & MATCH With Multiple Criteria - Select Table
  1. 5. Add the MATCH function and type “1” as the search key.
How to Use INDEX MATCH With Multiple Criteria Search Key 1
How to Use INDEX & MATCH With Multiple Criteria - Search Key 1
  1. 6. After the comma, add each of your vertical criteria between parentheses, joined by a multiplication sign.
How to Use INDEX MATCH With Multiple Criteria Vertical Criteria
How to Use INDEX & MATCH With Multiple Criteria - Vertical Criteria
  1. 7. After the comma, add “0” for an exact match and close the three parentheses.
How to Use INDEX MATCH With Multiple Criteria Exact Match 5
How to Use INDEX & MATCH With Multiple Criteria - Exact Match
  1. 8. Press ‘Enter’ to see the result.
How to Use INDEX MATCH With Multiple Criteria Multiple Criteria Average Price
How to Use INDEX & MATCH With Multiple Criteria - Multiple Criteria Average Price

Conclusion

INDEX and MATCH offer a more flexible way of performing lookups in Microsoft Excel and Google Sheets. You can quickly look up values in complex tables using multiple vertical and horizontal criteria. You now know how to use INDEX and MATCH to get values based on both vertical and horizontal criteria, including multiple of each. You also know how to combine the INDEX and MATCH functions with others so that you can incorporate the calculation directly into your lookup formula.

To learn more about other lookup functions, check out these guides on:

Hady ElHady
Hady is Content Lead at Layer.

Hady has a passion for tech, marketing, and spreadsheets. Besides his Computer Science degree, he has vast experience in developing, launching, and scaling content marketing processes at SaaS startups.

Originally published Feb 8 2023, Updated Jun 26 2023

Layer is now Sheetgo

Automate your procesess on top of spreadsheets