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


3 comments

Tania Louie 6 years ago

Hi There,

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

Tania


almurray profile image

almurray 6 years ago from Ipswich, United Kingdom Author

Thanks Tania.

I'm glad it helped.


White Beard 5 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