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.
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.
More by this Author
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...
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...
If you are using Microsoft Project to schedule and track your projects, at some point you will probably want to export your project data into Excel. Excel can then be used to analyse and summarise your project data...