Lookup Data Based on Multiple Conditions in Excel

Being able to look up and return values from a table is an important skill in Excel. Popular functions for this task include the use of Vlookup, Hlookup, Index and Match to name a few. This article looks at how to look up data based on multiple conditions in Excel.

Using the data shown below, we plan to look up the value for the Training department in India. Download the lookup_multiple_conditions.xls spreadsheet to follow along.

Lookup data table
Lookup data table

To do this we will use an array formula with the Index and Match functions. Array formulas are sometimes called CSE formulas as you should press Ctrl + Shift + Enter when running the formula, as opposed to just Enter.

The Index and Match functions can be used together for looking up data in Excel. They are often seen as a better option to the Vlookup function, which has its limitations. Check out the Use Index and Match in Excel article.

Entering the Formula

Enter the formula below in cell F3. Make sure you press Ctrl + Shift + Enter when confirming the formula. Excel will place curly braces around the array formula.

=INDEX(C2:C17,MATCH(1,(A2:A17=F1)*(B2:B17=F2),0))

In the formula above the Match function finds and returns the row number where the text Training appears in column A, and the text India appears in column B. The Index function returns the value from column C for the row found by the Match function.

The value £21,300 should be returned.

Array formula to look up data using multiple criteria in Excel
Array formula to look up data using multiple criteria in Excel

More by this Author

  • Sort Pictures in an Excel List
    0

    If you are using pictures in your Excel spreadsheet you may be having some difficulty with sorting the pictures along with the other data in the list. The good news is that you can sort pictures in an Excel list just...

  • Create an Interactive Excel Chart using Option Buttons
    6

    Form controls can be added to an Excel spreadsheet to create interactivity with the user. This article looks at using option button controls to allow a user to choose the data they want to see on a chart. The first...

  • Impaction in Bearded Dragons
    10

    Impaction is when a bearded dragon’s digestive tract has become blocked. It is a very real hazard that is brought about when the beardie has either swallowed something that is too large, or something that it...


3 comments

Tania Louie 5 years ago

Hi There,

This was really useful, thanks so much for posting it!

Tania


almurray profile image

almurray 5 years ago from Ipswich, United Kingdom Author

Thanks Tania.

I'm glad it helped.


White Beard 4 years ago

Thanks, Al, this is what I have been trying to figure out for 3 days!!!

    Sign in or sign up and post using a HubPages Network account.

    0 of 8192 characters used
    Post Comment

    No HTML is allowed in comments, but URLs will be hyperlinked. Comments are not for promoting your articles or other sites.


    Click to Rate This Article
    working