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.
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.
Excel Lookup Tutorial Links
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.
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.