Do you want to take your Google Sheets skills to the next level? If you’re already familiar with the power of INDEX-MATCH in extracting data, then you’re in for a treat. In this article, we’ll show you how to amp up your game and use INDEX-MATCH with multiple criteria in Google Sheets.
Unleashing the Power of INDEX-MATCH
Before we delve into the details, let’s quickly recap what INDEX-MATCH is all about. This dynamic duo allows you to retrieve data from your spreadsheets with unmatched precision. If you’re not yet familiar with it, check out our in-depth guide on “Mastering INDEX-MATCH in Google Sheets” on Crawlan.com.
Now, let’s take it up a notch and explore how we can use INDEX-MATCH with multiple criteria in Google Sheets.
Using INDEX-MATCH with Multiple Criteria
To illustrate our example, let’s consider the following movie database:
Here, we have a small section of a movie database. Below that, we have our criteria or conditions, which in this case are the director’s name and genre.
Our criteria will be used to extract the movie’s name.
To perform an INDEX-MATCH with multiple criteria, we need the following formula syntax:
Formula Explanation:
We’ll start from the inside and work our way out.
-
MATCH: This function returns the position of the matching criteria.
- 1: This is the fixed lookup key.
- (criterion_1)(criterion_2)…(criterion_N): Our multiple criteria/conditions. The asterisk (*) acts as the logical AND to combine all the criteria into a single matching condition.
- 0: This defines that we are looking for an exact match, making it our fixed search type.
-
INDEX: Retrieves a value from a given range based on the row and column reference.
- range_reference: The range of cells containing the values that will be returned if there’s a match.
Therefore, our formula to extract the movie’s name based on the given criteria would be:
=INDEX(D3:D8, MATCH(1, (C3:C8="director_name")*(D3:D8="genre"), 0))
Feel free to try it out with different conditions:
But what happens if we give a condition that doesn’t exist in our dataset?
Well, the answer is simple: we get an #N/A error.
To overcome this error, we will replace it with a meaningful message. This can be achieved using the IFERROR function.
So, the modified formula becomes:
=IFERROR(INDEX(D3:D8, MATCH(1, (C3:C8="director_name")*(D3:D8="genre"), 0)), "Movie not found")
In case of an error, the IFERROR function will return a meaningful message.
An Alternative Approach
Instead of using the asterisk (*), we can also use the ampersand (&) to concatenate our criteria. However, in doing so, we need to make some modifications to our INDEX-MATCH formula.
Therefore, our updated formula becomes:
=INDEX(D3:D8, MATCH(CONCATENATE(C10:C11), C3:C8&D3:D8, 0))
Formula Explanation:
-
C3:C8&D3:D8: Combines all instances of criteria rows into one so that it can be used in MATCH. The output is presented as an array of all the rows.
-
CONCATENATE(C10:C11): Combines the criteria into a single value to aid in checking all instances in the MATCH function.
The rest of the function remains the same as discussed in the previous section.
As you can see from our formula explanation, we used the ARRAYFORMULA function to wrap our formula to present the values of all instances as an array. You can do the same for the final INDEX-MATCH formula; in fact, it is recommended.
So, instead of just pressing Enter to enter the formula, press CTRL+SHIFT+ENTER to automatically present your formula as an array. Or you can simply enter it manually.
In Conclusion
That wraps up all the ways we can use INDEX-MATCH with multiple criteria in Google Sheets! Although we showcased our examples with just two criteria, the formula allows for many more.
We hope our formula breakdown has helped you better understand the potential of INDEX-MATCH and why, in some cases, it’s the preferred choice over VLOOKUP.
If you have any questions or tips, feel free to leave them in the comments section below.
Don’t forget to check out Crawlan.com for more amazing Google Sheets tips and tricks!
