Match Multiple Criteria in Excel

Matching multiple conditions is possible to perform by using multiple logical conditions. This article will take you through matching multiple criteria in Excel.

You are free to use this image on you website, templates, etc., Please provide us with an attribution linkHow to Provide Attribution?Article Link to be HyperlinkedFor eg:Source: Excel Match Multiple Criteria (wallstreetmojo.com)

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. The three functions are IF, AND, and OR conditions.

For example, let us look at the calculation below of the AND formula.

Look at the formula below; we have tested whether the cell A1 value is >20 or not, the B1 cell value is >20 or not, and the C1 cell value is >20.

In the above function, all the cell values are >20, so the result is “TRUE.” We will change one of the cell values to less than 20.

The B1 cell value changed to 18, less than 20, so the result is “FALSE,” even though the other two cell values are >20.

We will apply the OR function in excelOR Function In ExcelThe OR function in Excel is used to test various conditions, allowing you to compare two values or statements in Excel. If at least one of the arguments or conditions evaluates to TRUE, it will return TRUE. Similarly, if all of the arguments or conditions are FALSE, it will return FASLE.read more for the same number.

For the OR function, one criterion needs to be satisfied to get the result as “TRUE.” So we need all the conditions to meet the “TRUE” result, unlike the AND function.

Example #2 – Multiple Logical Functions in Excel

Now, we will see how to perform multiple logical functions to match multiple conditions.

  • Let us look at the below data in Excel.

We need to calculate the “Bonus” amount based on the below conditions from the above data.

If the “Year of Service” is >1.5 years and if the “Department” is “Sales” or “Support,” then the bonus is $8,000; otherwise, the bonus will be $4,000.

So, to arrive at 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 whether the “Department” is “Sales” or “Support” to arrive at the bonus amount.

  • We must first open the 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 mandatory “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 a 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.

  • If any department matches these criteria, we need the “TRUE” result, so we need to use the OR function.

As you can see above, we have used AND and OR functions to match single criteria. So, for example, the AND function will test the year of service at>1.5 years, and the 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,” the logical test result will be “TRUE” or else “FALSE.”

  • If the logical test is “TRUE,” we need the bonus amount at $8,000. So for the value, if True argument, we need to supply #8,000.

  • If the logical test is “FALSE,” we need the bonus amount at $4,000.

  • So, we are done with applying the formula, closing the formula, and applying the formula to other cells to get the result in other cells.

Now, let us look at the fourth row. In this case, the “Year of Service” is 2.4, and the “Department” is “Support,” so the bonus arrives at $8,000. Like this, we can match multiple criteria in Excel.

Things to Remember

  • To test multiple criteria to arrive at a single result, we need to use multiple logical functions inside the IF condition.The AND and OR Excel functions are the two supporting functions we can use to test multiple criteria.The AND function in Excel may return “TRUE” only if all the logical tests are satisfied, but on the other hand, the OR function requires at least one logical test to be met to get a “TRUE” result.

This article has been a guide to Excel Match Multiple Criteria. Here, we discuss matching multiple criteria in Excel using AND, IF, and OR formulas and practical examples. You may learn more about Excel from the following articles: –

  • Excel Break-Even AnalysisMatch Function Excel ExamplesVLOOKUP with Multiple CriteriaSUMPRODUCT with Multiple CriteriaNETWORKDAYS Excel Function