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.

**Read more:** Index Match Multiple Criteria in Rows and Columns in Excel

## Breakdown of Functions Used in This Article

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.

You can learn about this function in detail by reading this documentation from Microsoft.

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

You can learn about this function in detail by reading this documentation from Microsoft.

### 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

You can learn about this function in detail by reading this documentation from Microsoft.

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

You can learn about this function in detail by reading this documentation from Microsoft.

## Download the Workbook

You can download the workbook that I used in this article from below and practice with it by yourself.

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

**Read more:** How to Match Multiple Criteria from Different Arrays in Excel

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.

## Further Readings:

- INDEX, MATCH and MAX with Multiple Criteria in Excel
- INDEX MATCH Multiple Criteria in Excel (Without Array Formula)
- Index Match Sum Multiple Rows
- Sum with INDEX-MATCH Functions under Multiple Criteria in Excel
- Excel Index Match single/multiple criteria with single/multiple results
- How to use INDEX & MATCH worksheet functions in Excel VBA