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?