Match Multiple Criteria in Excel
Criteria based calculations in excel are performed by logical functions. To match single criteria we can use IF logical condition, having to perform multiple tests we can use nested if conditions. But imagine the situation of matching multiple criteria to arrive single result is the complex criteria based calculation. To match multiple criteria in excel one needs to be an advanced user of functions in excel.
Matching multiple conditions is possible to perform by making use of multiple logical conditions. In this article, we will take you through how to match multiple criteria in excel.
How to Match Multiple Criteria Excel? (Examples)
Example #1 – Logical Functions in Excel
Commonly we use three logical functions in excel to perform multiple criteria calculations and those three functions are IF, AND, & OR conditions.
For example, look at the below calculation of AND formula.
Look at the below formula, we have tested whether cell A1 value is >20 or not, the B1 cell value is >20 or not, the C1 cell value is >20 or not.
In the above function, all the cell values are >20 so the result is TRUE. Now I will change one of the cell values less than 20.
B1 cell value changed to 18 which is less than 20 so the end result is FALSE, even though the other two cell values are >20.
4.9 (1,353 ratings) 35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
Now for the same number, I will apply the OR function in excel.
For OR function any one criteria need to satisfy to get the result as TRUE, this is unlike AND function where we need all the conditions to be satisfied to get the TRUE result.
Example #2 – Multiple Logical Functions in Excel
Now we will see how to perform multiple logical functions to match multiple conditions.
- Look at the below data in excel.
From the above data, we need to calculate the “Bonus” amount based on the below conditions.
If the year of service is >1.5 years and if the “Dept.,” is “Sales” or “Support” then the bonus is $8,000 otherwise the bonus will be $4,000.
So, to arrive one result we need to match multiple criteria in excel. In this case, we need to test whether the year of service is 1.5 and also to test whether the department is “Sales” or “Support” to arrive at the bonus amount.
- First open condition in excel.
- The first logical condition to test is compulsory whether the year of service is >1.5 years or not. So for the compulsory TRUE result, we need to use AND function.
The above logical test will return TRUE only if the year of service is >1.5 otherwise we will get FALSE as the result. Inside this AND function we need to test one more criterion i.e. whether the department is “Sales” or “Support” to arrive at a bonus amount.
- In this case, if anyone department matches these criteria we need the TRUE result, so we need to use OR function.
As you can see above we have used AND & OR functions to match single criteria. AND function will test year of service is >1.5 years, and OR function will test whether the department is Sales or Support. So if the Year of Service is 1.5 and the department is either Sales or Support logical test result will be TRUE or else FALSE.
- Now if the logical test is TRUE then we need the bonus amount as $8,000. So for Value, if True argument we need to supply #8,000.
- If the logical test is FALSE then we need the bonus amount at $4,000.
- So, we are done with applying the formula, close the formula and apply the formula to other cells as well to get the result in other cells.
Now look at the 4th row, in this case, the Year of Service is 2.4 and the Department is “Support” so the bonus is arriving at $8,000. Like this, we can match multiple criteria in excel.
Things to Remember
- To test multiple criteria to arrive single result we need to use multiple logical functions inside the IF condition.
- AND & OR are the two supporting functions we can use to test multiple criteria.
- AND will return TRUE only if all the logical tests are satisfied but on the other hand OR requires at least one logical test to be satisfied to get a TRUE result.
This has been a guide to Excel Match Multiple Criteria. Here we discuss how to match multiple criteria in excel using AND, IF and OR formulas along with practical examples. You may learn more about excel from the following articles –