Multiple Criteria in Excel Using INDEX, MATCH, and COUNTIF Function

Finding cells that satisfy multiple criteria is a typical task in Excel. There are many ways to accomplish this task. In this article, I have discussed four formulas that find the cells with multiple criteria. And these are using INDEX, MATCH, and COUNTIF functions.

The formulas that I used here uses the following functions:

The INDEX function:

This function gets values in a list or table based on location. The syntax of the INDEX function is as follows.

=INDEX (array, row_num, [col_num], [area_num])

array: Range of cells, or an array constant.

row_num: The row position in the reference.

col_num [optional]: The column position in the reference.

area_num [optional]: The range in reference that should be used.

The MATCH function:

This function gets the position of an item in an array. The syntax of the MATCH function is as follows.

=MATCH (lookup_value, lookup_array, [match_type])

lookup_value: The value to match in lookup_array.

lookup_array: A range of cells or an array reference.

match_type [optional]:Â  1 = exact or next smallest, 0 = exact match, -1 = exact or next largest. By default, match_type=1.

The COUNTIFS function:

This function counts cells that match multiple criteria. The syntax of the COUNTIFS function is as follows.

=COUNTIFS (range1, criteria1, [range2], [criteria2], â€¦)

range1 â€“ The first range to evaluate.

criteria1 â€“ The criteria to use on range1.

range2 [optional]: The second range to evaluate.

criteria2 [optional]: The criteria to use on range2. Up to 127 range/criteria pairs are allowed

The SUM function:

This function takes values and adds them up. The syntax of the SUM function is as follows.

=SUM(number1, [number2] ,â€¦)

The SUM function takes 3 types of inputs: positive or negative numeric values, range, and cell references. It takes these inputs and shows their summation as output. The first argument is mandatory, others are optional, and it takes up to 255 numbers.

In this dataset, I have tried to provide a real-life example. The dataset contains information about some clothing products. It has four columns, the name of the product, the Color, the Size, and the Price as you can see in the following image.

4 Formulas to Find Cells with Multiple Criteria in MS Excel

1. Using the INDEX and MATCH function (Array Formula)

Here, I have fetched the Price of the Product (Cell B11) based on the productâ€™s Name, Color, and Size.

The formula is as follows:

`{=INDEX(E14:E34,MATCH(1,(B8=B14:B34)*(B9=C14:C34)*(B10=D14:D34),0))``}`

Breakdown:

The multiplication operation:

• (B8=B14:B34)*(B9=C14:C34)*(B10=D14:D34)
• (Shirt = Product Column)*(Indigo = Color Column)*(L = Size Column
• {FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}*(B9=C14:C34)*(B10=D14:D34)}

[It will search the values to the respective column and return TRUE/FALSE values according to it.]

• {0;0;0;0;0;0;0;0;0;0;0;0;1;0;0;0;0;0;0;0;0}

[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 .]

The MATCH function:

MATCH(1,(0;0;0;0;0;0;0;0;0;0;0;0;1;0;0;0;0;0;0;0;0),0))

• 13

[This function looks for the value 1 in the converted range and returns the position.]

The INDEX function:

INDEX(E14:E34,13)

• 50

[This function returns the value in the 13th row of the price column which is the desired output.]

2. Using the INDEX and MATCH function (Non-Array Formula)

Here, I have tried to perform the same task as before. The formula is also the same except there is an extra INDEX function.

The formula is as follows:

`=INDEX(E14:E34,MATCH(1,INDEX((B8=B14:B34)*(B9=C14:C34)*(B10=D14:D34),0,1),0))`

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.

3. Using the COUNTIFS function

3.1. AND logic

AND logic means that all criteria should be matched to get the true value.

Here, I have calculated the total number of rows based on the Name, Color, and Size criteria.

The formula is as follows:

`=COUNTIFS(B14:B34,B8,C14:C34,B9,D14:D34,B10)`

Breakdown:

COUNTIFS(B14:B34,B8,C14:C34,B9,D14:D34,B10)

• COUNTIFS(Product Column, Shirt, Color Column, Indigo, Size Column, L)

[It searches for the values in the respective columns and increases the count if all the criteria are matched.]

• 1

[There is only one column where all the criteria match. So, it is the desired output.]

3.2 OR logic

OR logic means that if one criteria matches, the TRUE value will be returned.

Here, I have calculated the total number of rows where the color values are â€śRedâ€ť and â€śYellowâ€ť.

The formula is as follows:

`=SUM(COUNTIFS(C11:C31,{"Red","Yellow"}))`

Breakdown:

SUM(COUNTIFS(C11:C31,{â€śRedâ€ť,â€ťYellowâ€ť}))

• SUM(COUNTIFS(Color column,{â€śRedâ€ť,â€ťYellowâ€ť}))

[It searches for the values in the respective column and increases the count if any criteria are matched.]

• SUM(3,3)

[As there are three â€śRedâ€ť and three â€śYellowâ€ť, thatâ€™s why the COUNTIF function returns 3,3.]

• 6

[The SUM function adds the two values and returns the desired output.]

Conclusion

I have narrowed down four formulas to find cells with multiple criteria using the INDEX, MATCH, and COUNTIFS functions in this article. I hope you were able to find a solution to your problem. Please leave a comment if you have any suggestions or questions. Thank you.