This guide will show you the Google Sheets Index Match with Multiple Criteria functionality. This function helps you easily look up values within a sheet. This means the function will index match values based on multiple criteria instead of only one.
The Google Sheets index match multiple criteria functionality is often regarded as a great alternative to the LOOKUP, HLOOKUP, or VLOOKUPÂ functions. To demonstrate how the formula works, I will provide a breakdown of the syntax, its uses, and a video tutorial to guide you through each step of achieving the desired outcome with this Google Sheets match multiple criteria functionality.
For purposes of additional clarity, if you haven’t seen our previous article detailing the INDEX and MATCH functionality, I highly recommend that you do so before reading further.
Table of Contents
Google Sheets Index Match Multiple Criteria Syntax
When combining INDEX and MATCH in Google Sheets, you will begin with the INDEX function’s formula. The MATCH portion of the formula fills the position argument.
The syntax for the basic INDEX MATCH formula is as follows:
INDEX(return array, MATCH(lookup value, lookup array))
When combined, the two formulas can look up a value in a cell from a table and return the corresponding value in another cell in the same row or column.
Now let’s get into the syntax for the INDEX MATCH formula in multiple criteria. You can use the following basic syntax to perform an INDEX MATCH in Google Sheets with multiple criteria:
=INDEX(reference,MATCH(1,(criteria1)*(criteria2)*(criteria3),0))
where:
- reference:Â The range from which a value will be returned
- MATCH:Â Gives the position of your search key
- 1:Â Specifies a fixed search key
- criteria1, criteria2, criteria3:Â The criteria to match
- 0:Â Species to search for an exact value
Using INDEX MATCH in Google Sheets with Multiple Criteria
Let’s take a look at the following spreadsheet, it shows the different salaries for Doctors, Mechanics, and Lawyers based on their countries (USA, UK, Canada).
Now, we are going to use the Google Sheet Index Match Multiple Criteria function to look up different values by matching the corresponding entries in the table. For example, we want to check the salary of a doctor in the UK. That function would contain two criteria, the profession (which is a doctor) and the country (which is the UK).
Using the Index Match Multiple Criteria Google Sheets function, we can easily carry out this simple task. So, Let’s begin (you can download our sample file to follow along).
Step 1: Add a dropdown list to cells A13 and B13. This will make it easier to change the criteria and look up the corresponding values.
To do this, right-click on cell A13, go to view more cell actions, and click on Data Validation.
Step 2: Once in the Data validation menu, select List from a range, and select A2:A10.
Once you have done this for cell A13, do the same for cell B13.
Step 3:Â Use the =INDEX(reference,MATCH(1,(criteria1)*(criteria2)…,0))Â to index match on multiple criteria.
In the screenshot above, I have used the Google Sheets match multiple columns function: =INDEX(C:C,MATCH(1,(A:A=A13)*(B:B=B13),0))
This formula will match with multiple criteria and lookup the corresponding values based on the values selected from the dropdowns in cell A13 and B13.
Now as you can see, thanks to Google Sheets INDEX MATCH with multiple results will show up depending on the values selected in cells A13 and B13:
And done! That’s how simple it is to use this function!
Frequently Asked Questions
Can You Combine the IF Function with INDEX Match?
While working in both Google Sheets and Excel, we often have to combine these three functions.
In order to combine the functions to do whatever you want to do, you must first Wrap INDEX MATCH within an IF Function to Use IF with INDEX MATCH in Excel or Sheets. And if that does not work, you can try to Use IF Function.
How Many Criteria Can INDEX MATCH Have?
Google Sheets or Microsoft Excel provide special functions for vertical and horizontal lookup. Expert users normally replace them with INDEX MATCH, which is superior to VLOOKUP and HLOOKUP in many ways.
Now that we know there are various criteria that can be added to the index match. The number of criteria index and match can have two or more criteria in columns and rows. You can have a seemingly infinite number of criteria.
Conclusion
The guide above provides a detailed look into how I used this function to Index match with multiple criteria Google Sheets from the provided table. Needless to say, this function can support numerous criteria (although we used only two), and you can use it to match and look up from any relevant Google Sheet easily.
There can be various real-life situations like the one above where you have to match and lookup values based on different criteria from a single sheet. Although in a small-sized sheet like the above, this is pretty straightforward, it may get very complicated to do so in a larger sheet with hundreds of entries. In this case, the Google Sheets index match multiple criteria function comes in very handy. Hopefully, this guide was enough to ease your use of this function!
Related:
