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

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.

Example 2

###### 2 comments on “Tom’s Tutorials For Excel: LOOKUP With Multiple Criteria”
1. Emma says:

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

2. Eugeny says:

[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?

###### 1 Pings/Trackbacks for "Tom’s Tutorials For Excel: LOOKUP With Multiple Criteria"
1. […] Today’s post from Tom Urtis involves finding the record that matches two criteria, such as Chevrolet in column A and 4-Door in column B. The solution involves INDEX, MATCH entered as an array formula, so this is ranked pretty high on the Excel spicy scale. Read the complete article here. […]