Tom’s Tutorials For Excel: LOOKUP With Multiple Criteria


Share Button

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

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,
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. […]

Leave a Reply

Your email address will not be published. Required fields are marked *

*