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
MATCH functions to invoke two criteria that return the price of a particular automobile.
The formula in cell B26 is
It’s an array formula, applied to the cell by pressing Ctrl+Shift+Enter, not just Enter.
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
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?