Excel Match Multiple Criteria

Logical functions perform criteria-based calculations in Excel. To match single criteria, we can use the IF logical condition. Having to perform multiple tests, we can use nested IF conditions. But imagine the situation of matching multiple criteria to arrive at a single result is the complex criteria-based calculation. To compare various criteria in Excel, one needs to be an advanced user of functions 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.

In Excel, we can match multiple criteria using IF, AND and OR functions. Let us learn how to match multiple criteria in Excel with the following detailed 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 excel 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.

Example 3

Consider the below example showing the marks obtained by a student in 3 different tests.

Since the pass make is 40; let us check the marks using MATCH multiple criteria in Excel. 

First, let us insert the AND function formula, =AND(A2>40,B2>40,C2>40) in cell D2.

Press Enter key. We can see the result as shown in the following image.

Using Autofill, we can find the option for cell D3 as shown in the below image.

Now, let us find the result using OR function.

Insert the OR formula =OR(A2>40,B2>40,C2>40) in cell E2.

Press Enter key. We can see the OR function result as shown in the below image.

Similarly, we can use AutoFill option to find the result in cell E3 as shown in the below image.

We can see that the result in cell D3 and E3 are different.

This is because, the AND function returns the answer as TRUE only if all the arguments and the criteria are met.

But, the OR function returns TRUE even if one of the arguments meets the criteria.

Likewise, we can use MATCH multiple criteria in Excel.

• 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.