INDEX, MATCH, and COUNTIF Functions with Multiple Criteria

Finding cells that satisfy multiple criteria is a typical task in Excel. There are many ways to accomplish this task. In this article, I will show you how to apply the INDEX, MATCH, and COUNTIF functions with multiple criteria in Excel.

INDEX, MATCH, and COUNTIF Functions with Multiple Criteria: 4 Easy Ways

In this article, you will see four easy ways to apply the INDEX, MATCH, and COUNTIF functions with multiple criteria in Excel. First, I will use the combination of INDEX and MATCH functions in an array formula to select an item based on multiple criteria. Secondly, I will use the above two functions again, this time in a non-array formula for the same purpose as my second method. Thirdly, I will combine INDEX, MATCH, and COUNTIFS functions to select items. Finally, I will utilize the COUNTIFS function with AND and OR logic to serve the same purpose.

To illustrate my article further, I will use the following sample data set.

1. Combine INDEX and MATCH Functions in Array Formula with Multiple Criteria

In the first method, I will combine the INDEX and MATCH functions in an array formula with multiple criteria. Generally, an array is a group of things. The elements may take the form of text or numeric values, and they can place themselves in a single row, a single column, or numerous rows and columns. For the detailed procedure, see the following steps.

Steps:

• First of all, make eight new spaces above the main data set and fill in the first three criteria by taking information from the main data set according to your choice.

• Secondly, insert the following formula of the INDEX and MATCH function in cell D7.

• Thirdly, press Enter and you will see the price of the product that matches the criteria in cells D4, D5, and D6.

2. Combine INDEX and MATCH Functions in Non-Array Formula with Multiple Criteria

In the second method, I will again combine the INDEX and MATCH functions this time in a non-array formula. The formula here is also the same except there is an extra INDEX function. The main purpose of this new INDEX function is to convert the previous array formula to a non-array formula so that it can be implemented by someone who is not familiar with Excel array functions. The new INDEX function handles the returned array after the multiplication operation eliminating the need for an array formula. Go through the following steps for a better understanding.

Steps:

• First of all, to find out the price of the product to the given criteria, use the following combination formula in cell D7.

• Secondly, after pressing Enter, you will find the price of the shirt in indigo color and size L.

3. Combine COUNTIFS, INDEX, and MATCH Functions for Multiple Criteria

In the previous two methods, you saw the combination of INDEX and MATCH functions based on multiple criteria to find an item. In this section, I will combine COUNTIFS, INDEX, and MATCH functions of Excel to count based on multiple criteria. See the below-given steps for a better understanding.

Steps:

• First of all, take the following data set with the necessary information.
• Here, I will determine the sales of each product in a day with the combination of above-mentioned functions.

• Secondly, before applying the formula, I will convert the primary data set into a table.
• For that, firstly, select data range B9:E18 and go to the Insert tab of the ribbon.
• Then, from the Tables group, choose Table.

• Thirdly, in the Create Table dialog box, check the data range and press OK.

• Fourthly, the above step will convert your data range into a table.
• Consequently, insert the following combination formula in cell D5 to find out the number of sales of coats in a day.

• Finally, press Enter to see the desired value, and to see the result for the other two products, use AutoFill.

4. Utilize COUNTIFS Function with Different Logics for Multiple Criteria

As per the previous discussion, the COUNTIFS function will return the number of cells based on single or multiple criteria. In this section, I will utilize the COUNTIFS function with different logic for multiple criteria.

4.1 COUNTIFS Function with AND Logic

First of all, I will utilize COUNTIFS with AND logic. The AND logic means that all criteria should be matched to get the actual value. For a better understanding, go through the following steps.

Steps:

• First of all, take the following data set, where I want to find out the number of products that match with all the criteria in cell C4, C5, and C6.
• To do that, type the following formula in cell C7.

• Secondly, after pressing Enter, the formula will show the number of products that match the given criteria.

4.2 COUNTIFS Function with OR Logic

Lastly, I will utilize COUNTIFS with the OR logic. OR logic means that if one criterion matches, the TRUE value will be returned. Go through the following steps for a better understanding.

Steps:

• Firstly, I want to count all the cells that contain yellow and red as cell values.
• For doing that, use the following formula in cell C4.

• Finally, press Enter to see the desired number of cells that contains the above two colors.

Things to Remember

• If you are not using the Microsoft Excel 365 version, then press Ctrl +Shift + Enter, while inserting the array formula.
• Remember to give proper table references, otherwise, you will get the #N/A error.

Download Practice Workbook

You can download the free Excel workbook here and practice on your own.

Conclusion

That’s the end of this article. I hope you find this article helpful. After reading the above description, you will be able to apply INDEX, MATCH, and COUNTIF functions with multiple criteria in Excel. Please share any further queries or recommendations with us in the comments section below. Therefore, after commenting, please give us some moments to solve your issues, and we will reply to your queries with the best possible solutions ever.

<< Go Back to Multiple Criteria | INDEX MATCH | Formula List | Learn Excel