One of the juiciest examples of combining formulas is using Index and Match. This powerful combo is popular in spreadsheet applications like Excel and Google Sheets for vertical lookup. And guess what? You can even use multiple conditions in Index Match in Google Sheets, making it even more versatile!
Multiple Conditions in Index Match in Google Sheets
Let me show you an example of how you can use more than one condition in Index Match. Imagine you have a list of smartphone products with different variants based on color. Now, let’s say you want to find the price of a specific smartphone with the color “Gold” and the status “Available.”
Here’s the formula to lookup multiple conditions in Index Match in 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.
The Match Formula Part
The Match formula is the key to handling multiple conditions. In this formula, we combine the conditions to use as the search key. It looks something like this:
=concatenate(G1:G3)For our example, the search key will be “Smartphone 1GoldAvailable.”
Next, we create a range to search in by combining the corresponding columns:
=ArrayFormula(A2:A7&B2:B7&C2:C7)This will give us a one-column range to work with.
Now, the Match formula in the Master Index Match formula above looks for the search key “Smartphone 1GoldAvailable” in the range we just created and returns the relative position, which is #1 in this case.
The Index Formula Part
The Index formula is used to fetch the desired result based on the relative position from the Match formula. In our case, we want to retrieve the price, which is in column 4.
The formula looks like this:
=ArrayFormula(index(A2:D,1,4))Here, instead of using a fixed row number, we replace it with the Match formula to dynamically fetch the result.
That’s it! With this formula, you can easily handle multiple conditions in Index Match in Google Sheets.
Conclusion
While Index Match is a powerful tool, you can also achieve similar results using Vlookup. Here’s the formula for that:
=ArrayFormula(vlookup(CONCATENATE(G1:G3),{A2:A10&B2:B10&C2:C10,D2:D10},2,false))In this case, the range part is a little different. We create a virtual range with two columns, combining the first three columns into one and adding the price column. The Vlookup index column is 2.
But honestly, the above two formulas might not be the best solutions. We can solve the same problem with an easy-to-read FILTER formula. Check it out:
=filter(D2:D7,A2:A7=G1,B2:B7=G2,C2:C7=G3)Give it a try and see how it works for you!
Thanks for reading, my besties! If you want to explore more SEO tips and tricks, head over to Crawlan.com.
Stay awesome and happy spreadsheeting!
Note: This article is originally written in Vietnamese and has been adapted for your enjoyment.
