VLOOKUP with Multiple Values or Criteria Using INDEX and MATCH (How To)

Using INDEX and MATCH to VLOOKUP with Two Criteria

To allow MATCH to search for multiple criteria, we are going to change the way it looks for its result by making it an array formula.An array formula takes an array of values instead of a single one and checks each cell in the array until it finds a result.

Our old MATCH formula looked like this:

=MATCH(A2,C4:C1159,0)

It looked for the value of A2 in the table C4:C1159, and when it found it, it returned the location.

Now we are going to ask it to be creative:

=MATCH(1,(C4:C1159=A2)*(D4:D1159=B2),0)

We have asked MATCH to look for a value of 1. Instead of giving it an existing array to look through, we are asking it to build one from scratch. The new array checks all the values in C4:C1159 for one that matches A2 and all the values in D4:D1159 for one that matches B2. Where they both match, the array will have a 1 (a TRUE boolean result). Where they don’t both match, the array will have a 0 (a FALSE boolean result). Therefore, MATCH will return the location where the array matches 1 (when both of our criteria are true).

If this process doesn’t make sense to you, that’s okay. Just plug the new MATCH function into your INDEX/MATCH formula:

=INDEX(C4:H1159,MATCH(1,(C4:C1159=A2)*(D4:D1159=B2),0),6)

When you enter the formula, don’t just press ENTER. Press CTRL+SHIFT+ENTER to tell Excel that it is an array formula. You can tell you’ve done it right because the entered formula will be surrounded in curly braces {}.

Curly Braces

With that, your formula will be able to find the Combined Fuel Economy based on both the Model and the Displacement. You can use this technique for any number of criteria with INDEX and MATCH. Just add additional terms to the multiplication equation.