ArtsAutosBooksBusinessEducationEntertainmentFamilyFashionFoodGamesGenderHealthHolidaysHomeHubPagesPersonal FinancePetsPoliticsReligionSportsTechnologyTravel

Lookup Data Based on Multiple Conditions in Excel

Updated on November 16, 2010

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

Comments

    0 of 8192 characters used
    Post Comment

    • profile image

      Tania Louie 6 years ago

      Hi There,

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

      Tania

    • almurray profile image
      Author

      Alan Murray 6 years ago from Ipswich, United Kingdom

      Thanks Tania.

      I'm glad it helped.

    • profile image

      White Beard 5 years ago

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

    Click to Rate This Article