This guide describes the index matching feature with multiple criteria in Google Sheets. This function helps you easily search for values in your sheet. This means that the function indexes match values based on multiple criteria instead of just one.
The ability for a Google Sheets index to match multiple criteria is considered a great alternative to the LOOKUP, HLOOKUP, or VLOOKUP functions. To show you how formulas work, here’s a video tutorial that guides you step-by-step through syntax details and how to use it, as well as achieving your desired results using this Google Sheets multi-criteria matching feature. We will provide
For further clarity, if you haven’t yet seen the previous article that detailed the INDEX and MATCH features, I highly recommend doing so before continuing.
Contents
Google Sheets Index Match Multiple Criteria Syntax
If you want to combine INDEX and MATCH in Google Sheets, start with the formula for the INDEX function. The MATCH part of the expression fills in the positional arguments.
The basic INDEX MATCH expression syntax is:
INDEX(return array, MATCH(search value, search array))
By combining two formulas, you can look up a value in a table cell and return the corresponding value in another cell in the same row or column.
Now let’s look at the syntax of the INDEX MATCH expression with multiple conditions. You can do an INDEX MATCH with multiple criteria in Google Sheets using the following basic syntax:
=INDEX(reference,MATCH(1,(criterion 1)*(criteria 2)*(criteria 3),0))
where:
- See also: Range for which values are returned
- MATCH: indicates the position of the search key
- 1: Specify a fixed search key
- criteria1, criteria2, criteria3: matching criteria
- 0: Seed to search for exact value
Using INDEX MATCH in Google Sheets with Multiple Criteria
Take a look at the following spreadsheet. Different salaries for doctors, mechanics and lawyers are shown based on the country (US, UK, Canada).
Now use the Google Sheet Index Match Multiple Criteria function to find different values by matching corresponding entries in the table. For example, you want to check the salaries of doctors in the UK. This function includes her two criteria: profession (doctor) and country (UK).
The Index Match Multiple Criteria feature in Google Sheets makes this simple task a breeze. Let’s get started (download the sample files and follow the steps).
Step 1: Add a dropdown list to cells A13 and B13. This makes it easier to change the criteria and search for corresponding values.
To do this, right-click cell A13 to display more cell actions and click Data Validation.
Step 2: After entering the Data Verification menu, select List from Range and select A2:A10.
Once you’ve done this for cell A13, do the same for cell B13.
Step 3: Use =INDEX(reference,MATCH(1,(criteria1)*(criteria2)…,0)) to create an index that matches multiple criteria.
The screenshot above uses the Google Sheets Multi-Column Match function: =INDEX(C:C,MATCH(1,(A:A=A13)*(B:B=B13),0))
This formula matches multiple criteria and looks for the corresponding value based on the values selected from the dropdowns in cells A13 and B13.
As you can see, thanks to Google Sheets, depending on the values selected in cells A13 and B13, you will see an INDEX MATCH with multiple results:
And done! Using this feature is very easy!
Frequently Asked Questions
Can You Combine the IF Function with INDEX Match?
When working with both Google Sheets and Excel, it is often necessary to combine these three functions.
To combine functions to do what you want, you must first wrap the INDEX MATCH inside an IF function and use IF with INDEX MATCH in Excel or Spreadsheet. If that doesn’t work, try using the IF function.
How Many Criteria Can INDEX MATCH Have?
Google Sheets or Microsoft Excel provide special functions for vertical and horizontal searching. Advanced users typically replace these with INDEX MATCH. It is superior to VLOOKUP and HLOOKUP in many ways.
I found that you can add different criteria to the index match. The condition index and number of matches can have two or more conditions on columns and rows. A seemingly infinite number of criteria can be set.
Conclusion
The guide above details how to use this function to create an index that matches multiple criteria in Google Sheets from the provided table. Needless to say, this function can support a large number of criteria (but I’ve only used two here), which you can use to easily match or search from related Google Sheets.
In a real world situation like the one above, you may encounter different situations where you need to match and find values based on different criteria from one sheet. For small sized sheets like the one above this is pretty straightforward, but doing this for large sheets with hundreds of entries can get very complicated. In this case, the ability for the Google Sheets index to match multiple criteria is very useful. We hope this guide was enough to ease your use of this feature!
- How to get the last value of a column in Google Sheets
- How to get column letter in Google Sheets
- 3 Easy Ways to Add Subscripts and Superscripts to Google Sheets
- How to use indirect functions in Google Sheets
- REGEXMATCH function in Google Sheets
