Match multiple criteria excel

INDEX and MATCH with multiple criteria

Related functions

Summary

To lookup values with INDEX and MATCH, using multiple criteria, you can use an array formula. In the example shown, the formula in H8 is:

The result is $17.00, the Price of a Large Red T-shirt. This is an array formula and must be entered with with Control + Shift + Enter in Legacy Excel.

Note: In the current version of Excel, you can use the same approach with the XLOOKUP function.

Generic formula

Explanation

This is a more advanced formula. For basics, see How to use INDEX and MATCH.

Normally, an INDEX MATCH formula is configured with MATCH set to look through a one-column range and provide a match based on given criteria. Without concatenating values in a helper column, or in the formula itself, there’s no way to supply more than one criteria.

This formula works around this limitation by using boolean logic to create an array of ones and zeros to represent rows matching all 3 criteria, then using MATCH to match the first 1 found. The temporary array of ones and zeros is generated with this snippet:

Here we compare the item in H5 against all items, the size in H6 against all sizes, and the color in H7 against all colors. The initial result is three arrays of TRUE/FALSE results like this:

Tip: use F9 to see these results. Just select an expression in the formula bar, and press F9.

The math operation (multiplication) transforms the TRUE FALSE values to 1s and 0s:

After multiplication, we have a single array like this:

which is fed into the MATCH function as the lookup array, with a lookup value of 1:

At this point, the formula is a standard INDEX MATCH formula. The MATCH function returns 3 to INDEX:

and INDEX returns a final result of $17.00.

Array visualization

The arrays explained above can be difficult to visualize. The image below shows the basic idea. Columns B, C, and D correspond to the data in the example. Column F is created by the multiplying the three columns together. It is the array handed off to MATCH.

Non-array version

It is possible to add another INDEX to this formula, avoiding the need to enter as an array formula with control + shift + enter:

The INDEX function can handle arrays natively, so the second INDEX is added only to «catch» the array created with the boolean logic operation and return the same array again to MATCH. To do this, INDEX is configured with zero rows and one column. The zero row trick causes INDEX to return column 1 from the array (which is already one column anyway).

Why would you want the non-array version? Sometimes, people forget to enter an array formula with control + shift + enter, and the formula returns an incorrect result. So, a non-array formula is more «bulletproof». However, the tradeoff is a more complex formula.

Note: In Excel 365, it is not necessary to enter array formulas in a special way.

Источник

Excel Match Multiple Criteria

Match Multiple Criteria in Excel

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.

Table of contents

You are free to use this image on your website, templates, etc., Please provide us with an attribution link How to Provide Attribution? Article Link to be Hyperlinked
For 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.

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.

Recommended Articles

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 Match Multiple Criteria

Excel Match Multiple Criteria (Table of Contents)

Introduction to Match Multiple Criteria in Excel

As a data analyst, you always need to deal with multiple criteria and conditions 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.

Excel functions, formula, charts, formatting creating excel dashboard & others

• 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 sets 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, and D1 are greater than 16, all the criteria are satisfied, leading to a Boolean output as TRUE under cell E1.

Step 3: Change the value in cell B1 as 12 and see how the result in cell E1 is affected.

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, 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 of the below 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 a 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, a student is a 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 a 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 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, the 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 a downloadable excel template. You can also go through our other suggested articles –

Источник