Multiple Conditions in Index Match in Google Sheets

Hey besties! Today I want to spill the tea on one of the best combo formulas in Google Sheets – Index Match. It’s all the rage and people are obsessed with it, even when they can use Vlookup. In this tutorial, I’ll show you how this combo can handle multiple criteria. Get ready to level up your spreadsheet game!

Using Multiple Conditions in Index Match

Let’s dive right in with an example of using more than one condition in Index Match. Imagine you have a list of smartphone products, each with different variants based on color. You want to look up the price of a specific smartphone that is “Gold” and “Available”.

Check out the screenshot below to see the multiple conditions marked:

In this case, the smartphone you’re looking for is in row #2 and the price is $650.00. Now, how do we create a formula to lookup these three conditions? Let me spill the secret!

The Master Index Match Formula

Here’s the formula you can use in your Google Sheets:

=ArrayFormula(index(A2:D,match(concatenate(G1:G3),A2:A&B2:B&C2:C,0),4))

Let me break it down for you, babes. The Match formula part works like this:

1. Combine the conditions as the search_key in the Match formula:

   =concatenate(G1:G3)

   This will give you the search key “Smartphone 1GoldAvailable”.

2. Next, create the range for the Match formula:

   =ArrayFormula(A2:A7&B2:B7&C2:C7)

   This will return a one-column range of values.

The Match formula in the Master Index Match formula then looks for the search key “Smartphone 1GoldAvailable” in the range we just created and returns the relative position.

Now, for the Index formula part:

In this part, we want to return the price, which is in column 4. So the abbreviated version of our Master Index Match formula is:

=ArrayFormula(index(A2:D,1,4))

Replace the 1 (row) with the Match formula we just created.

That’s it, besties! You’ve learned how to use multiple conditions in Index Match in Google Sheets.

Conclusion

Now, I know some of you might be thinking, “Can’t I just use Vlookup for this?” Absolutely, babes! Here’s the formula for that:

=ArrayFormula(vlookup(concatenate(G1:G3),{A2:A10&B2:B10&C2:C10,D2:D10},2,false))

Similar to multiple criteria in Index Match, you can combine the conditions here to use in Vlookup. The range part is a little different though. Instead of using A2:D like in Index Match, we create a virtual range with two columns. The first three columns are combined into one and then we add the Price column. So the Vlookup Index column here is 2.

But, if you want an easy-to-read solution, I got you covered! Check out the FILTER formula below:

=filter(D2:D7,A2:A7=G1,B2:B7=G2,C2:C7=G3)

Thanks for hanging out with me, besties! Enjoy leveling up your Google Sheets skills with these multiple conditions in Index Match. You’re gonna slay it!

P.S. If you want more juicy secrets and tutorials, head over to Crawlan.com.

Stay fabulous and keep crushing it!