Tom’s Tutorials For Excel: LOOKUP With Multiple Criteria

by Tom Urtis 2 Comments ↓

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

‹ Tom’s Tutorials For Excel: The HLOOKUP Function

Tom’s Tutorials For Excel: Match From List ›

Posted in Tom's Tutorials for Excel
Tags: Analytics, Array Formulas, Big Data, Business Intelligence, Cells Ranges Lists, Data Science, Excel Expert, Excel Guru, Microsoft, Microsoft Excel, Microsoft MVP, Microsoft Office, Tom Urtis

2 comments on “Tom’s Tutorials For Excel: LOOKUP With Multiple Criteria”

1. Emma says:

   March 30, 2012 at 7:03 am

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

   Reply
2. Eugeny says:

   April 5, 2012 at 7:32 am

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

   Reply

1 Pings/Trackbacks for "Tom’s Tutorials For Excel: LOOKUP With Multiple Criteria"

1. Tom Urtis: Lookup with 2 or more criteria « VLOOKUP WEEK says:

   March 28, 2012 at 3:58 pm

   […] 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. […]