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.
Watch Video – 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.
=INDEX(E10:E28,MATCH(1,(D4=B10:B28)*(D5=C10:C28)*(D6=D10:D28),0))
Formula Breakdown
=INDEX(E10:E28,MATCH(1,(D4=B10:B28)*(D5=C10:C28)*(D6=D10:D28),0))
- (B8=B14:B34)*(B9=C14:C34)*(B10=D14:D34): Firstly, the multiplication operation will search the values to the respective column and return TRUE/FALSE values according to it.
- Then, the multiplication operator (*) converts these values to 0s and 1s and then performs the multiplication operation which converts all other values to 0s except the desired output.
- MATCH(1,(0;0;0;0;0;0;0;0;0;0;0;0;1;0;0;0;0;0;0;0;0),0)): Secondly, the MATCH function looks for the value 1 in the converted range and returns the position that is 13.
- INDEX(E14:E34,MATCH(1,(B8=B14:B34)*(B9=C14:C34)*(B10=D14:D34),0)): Finally, the INDEX function returns the value in the 13th row of the price column which is the desired output that is 50.
- 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.
=INDEX(E10:E28,MATCH(1,INDEX((D4=B10:B28)*(D5=C10:C28)*(D6=D10:D28),0,1),0))
Formula Breakdown:
=INDEX(E10:E28,MATCH(1,INDEX((D4=B10:B28)*(D5=C10:C28)*(D6=D10:D28),0,1),0))
- INDEX((D4=B10:B28)*(D5=C10:C28)*(D6=D10:D28),0,1): The INDEX function will search the values to the respective column and return TRUE/FALSE values according to it.
- Then, the multiplication operator (*) converts these values to 0s and 1s and then performs the multiplication operation which converts all other values to 0s except the desired output.
- MATCH(1,INDEX((D4=B10:B28)*(D5=C10:C28)*(D6=D10:D28),0,1),0): Secondly, the MATCH function looks for the value 1 in the converted range and returns the position that is 13.
- INDEX(E14:E34,MATCH(1,INDEX(B8=B14:B34)*(B9=C14:C34)*(B10=D14:D34),0,1),0)): Finally, the INDEX function returns the value in the 13th row of the price column which is the desired output that is 50.
- 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.
=COUNTIFS(INDEX(Table4,0,MATCH(B5,Table4[#Headers],0)),"x")
Formula Breakdown
=COUNTIFS(INDEX(Table4,0,MATCH(B5,Table4[#Headers],0)),”x”)
- MATCH(B5,Table4[#Headers],0): Firstly, the MATCH function uses the cell value of B5 as the lookup value, then the headers in Table4 for the array, and 0 for an exact match.
- INDEX(Table4,0,MATCH(B5,Table4[#Headers],0)): Secondly, the INDEX function will return the entire column for coat, which is C10:C18 in the above image.
- COUNTIFS(INDEX(Table4,0,MATCH(B5,Table4[#Headers],0)),”x”): Finally, the COUNTIFS functions will count cells where there is “x”, and will show the total number of cells as the result.
- 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.
=COUNTIFS(B10:B28,C4,C10:C28,C5,D10:D28,C6)
Formula Breakdown
=COUNTIFS(B10:B28,C4,C10:C28,C5,D10:D28,C6)
- COUNTIFS(B10:B28,C4,C10:C28,C5,D10:D28,C6): The COUNTIFS function searches for the values in the respective columns and increases the count if all the criteria are matched.
- Consequently, there is only one column where all the criteria match. So, 1 is the desired output.
- 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.
=SUM(COUNTIFS(C7:C27,{"Red","Yellow"}))
Formula Breakdown
=COUNTIFS(B10:B28,C4,C10:C28,C5,D10:D28,C6)
- COUNTIFS(C7:C27,{“Red”,”Yellow”}): Firstly, the COUNTIFS function searches for the values in the respective columns and increases the count if any criteria is matched.
- SUM(COUNTIFS(C11:C31,{“Red”,”Yellow”})): Secondly, as there are three “Red” and three “Yellow”, that’s why the COUNTIF function returns 3,3.
- Then, the SUM function adds the two values and returns the desired output.
- Consequently, there is only one column where all the criteria match. So, 1 is the desired output.
- 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.
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.
Related Articles
- How to Match Multiple Criteria from Different Arrays in Excel
- Use INDEX MATCH for Multiple Criteria Without Array
- How to Use IF with INDEX & MATCH Functions in Excel
- Sum with INDEX-MATCH Functions under Multiple Criteria in Excel
- SUMIF with INDEX and MATCH Functions in Excel
- How to Sum Multiple Rows Using INDEX MATCH Formula
- SUMPRODUCT with INDEX and MATCH Functions in Excel
I have utilized Excel for at least a decade. I have ONE Hugh problem with your Excel Seminars. Where where you 10 years ago. Man could I have used your guildance then. You seminars are the best out there – every education and truly a learning experience. Thanks for taking the time and effort – You are a true asset to all.
Dear Seaunders,
Thanks for your appreciation. It means a lot to us.
Regards
ExcelDemy