Excel Match Multiple Criteria (Table of Contents)
Introduction to Match Multiple Criteria in Excel
Being a data analyst, you always need to deal with multiple criteria and conditions in order to get the desired result. In Excel, you can use the IF Statement for conditional outputs. However, at times you need to construct more sophisticated logical tests in order to get the desired results. It may include the multiple IF Statements (i.e. nested IF) or can also be achieved with the help of some logical operators used under IF statements. We ideally can construct multiple criteria’s using logical AND & OR operator under the IF Statement.
- AND Operator: If you use logical AND operator/keyword under IF Statement, you will get TRUE as a result if all conditions/criteria are satisfied. Otherwise, it will return FALSE.
- OR Operator: If you use logical OR operator/keyword under IF statement, you’ll get TRUE as a result if at least one of the multiple conditions/criteria are satisfied. Else it will return FALSE.
How to Match Multiple Criteria in Excel?
To Match Multiple Criteria in Excel is very simple and easy. Let’s understand this function with some examples.
Example #1 – AND/OR Operators Works for Multiple Criteria
Suppose we have three set of marks for a student as shown below:
Step 1: In cell E1, as we need to check how AND operator works for multiple criteria, start initiating the formula by typing “=AND(
Step 2: We need to specify logical criteria under AND function. Use criteria as cell value greater than 16 for all cells (B1, C1, D1). You can use a comma as a separator to separate the multiple criteria conditions. See the screenshot below:
Once you press Enter key, you can see TRUE as a result in cell E1. Since all values present in cell B1, C1, D1 are greater than 16, it means all the criteria are satisfied which leads to a Boolean output as TRUE under cell E1.
4.5 (303 ratings)
View Course
Step 3: Change the value in cell B1 as 12 and see how the result in cell E1 gets affected by doing so.
You can see with the same criteria under AND function, if we change the value under cell B1 as less than the criteria value (i.e. 16), we will get the output as FALSE. Since the value in cell B1 is not greater than 16 due to which not all the criteria are satisfied.
Step 4: In cell E2, use the OR to check how it works, use the same criteria values. Ideally OR gives Boolean output as TRUE if at least one of the criteria is satisfied.
Press Enter key and you can see the output as TRUE in cell E2. Since C1 and D1 have values greater than 16. However, with E1, it is still FALSE since not all the conditions are satisfied.
Example #2 – Complex Criteria in Combination with IF, AND/OR
Suppose, we conduct two tests in a class of 10 students and have Marks in both tests for each student stored under column Marks 1 and Marks 2 respectively. We wanted to check whether the student has passed the exam or not. See the screenshot below of data
In order to check whether the student has passed the test or he failed, we can use a combination of IF statement with AND/OR operator to combine the multiple criteria.
Suppose we have two criteria’s under this example:
Criteria 1: Column B >= 20 and Column C >= 23
Criteria 2: Column B >= 15 and Column C >= 18
If either one of the criteria is fulfilled, the student will be declared as Pass else he/she will be declared as Fail. Let’s try to figure this tricky criterion out with IF, AND, OR.
Step 1: In cell D2, initiate the formula for IF Statement by typing “=IF(
Step 2: Initiate an OR condition within the IF Statement as shown below:
Step 3: Now, we need to add two AND conditions within this OR condition separated by a comma. Use (AND(B2>= 20, C2>=23), AND(B2>=15, C2>=18)) under OR condition within IF Statement and close the bracket for OR condition.
This condition will first check if Marks 1 and Marks 2 are greater than or equals to 20 and 23 respectively (first AND condition) OR if not, it will check whether the Marks 1 and Marks 2 are greater than or equals to 15 and 18 (second AND condition). If at least one of these two conditions is satisfied, the student will be considered as Pass otherwise the student will be considered as Fail.
Step 4: Add the [value_if_true] as “Pass” under the formula in cell D2 to specify if at least one of the condition gets satisfied, student is pass.
Step 5: Add “Fail” as a value for [value_if_false] under the IF statement to complete the formula and close the brackets to complete it. Press Enter key to see the output in cell D2. Whether the student named “Martha” has passed the exam or not.
Step 6: Drag the formula across cells D2:D11 so that we will have result value either Pass or Fail for all the students named in column A. You can select D2:D11 and then press Ctrl + D as a keyboard shortcut to be able to copy the formula across rows.
This is how, we can match multiple criteria’s under Excel with the help of IF statement, AND & OR logical operators. This article ends here. Let’s wrap the things up with some points to be remembered.
Things to Remember
- Logical operators such as AND, OR in combination with conditional statement IF are used to match multiple criteria under Excel.
- Microsoft Excel tests all the conditions under AND even if the previous condition is checked and appeared as FALSE. This is a bit unusual as in other programming languages if the previously checked condition is returned as FALSE under logical AND, system stops checking other subsequent conditions.
- Logical AND returns TRUE if all the conditions inputted are satisfied. Otherwise, it returns as FALSE
- Logical OR returns TRUE if at least one of the conditions is satisfied. If no other condition is satisfied, it returns as FALSE.
Recommended Article
This has been a guide to Excel Match Multiple Criteria. Here we discuss how to Match Multiple Criteria along with practical examples and downloadable excel template. You can also go through our other suggested articles –
