**Tom’s Tutorials For Excel: LOOKUP With Multiple Criteria**

Sometimes you need to look up a value based on more than one criteria, a task for which `VLOOKUP`

falls short. In the picture labeled Example 1, see how you you can use the `INDEX`

and `MATCH`

functions to invoke two criteria that return the price of a particular automobile.

The formula in cell B26 is `=INDEX(C4:C21,MATCH(1,(A4:A21=B24)*(B4:B21=B25),0))`

.

It’s an array formula, applied to the cell by pressing Ctrl+Shift+Enter, not just Enter.

**Example 1**

OK, as if two lookup criteria are not enough, what if you have many more than just two?

In Example 2, you see a syntax for returning the price for many criteria. The formula in cell B29 is

`=INDEX(E4:E21,MATCH(B24&B25&B26&B27,A4:A21&B4:B21&C4:C21&D4:D21,0))`

.

Note that this is also an array formula — apply it with Ctrl+Shift+Enter.

I would always use a concatenate for this type of sum… but I like this as an alternative!

[the website i mentioned is not my own, but i like Regex Excel Add-in]

I always used establishing a helper column where i concatenate keys, so as to VLOOKUP along this column. Is it more resourse consuming? What do you think, guys?