Finding cells that satisfy multiple criteria from different arrays is a typical task in Excel. There are many ways to accomplish this task. In this article, I have discussed five formulas that find the cells with multiple criteria.

**Table of Contents**hide

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

## Breakdown of Functions Used in This Article

The formulas that I used here uses the following functions:

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

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

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

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

**5. The FILTER function:**

This function filter ranges with given criteria. The syntax of the **FILTER **function is as follows.

**=FILTER (array, include, [if_empty])****array:** The range or array to filter.

**include: **The boolean array, supplied as criteria.

**if_empty [optional]: **The value to return when no results are found.

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

## 5 Formulas to Match Multiple Criteria from Different Arrays 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:

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

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

`IFERROR(INDEX(E14:E34,13), "No Match")`

*=> 50*

*[This function returns the value in the 13th row of the price column which is the desired output. For cases where there are no matches, The INDEX function will return a N\A error. For handling such errors and displaying a human-readable message, “No Match”, the IFERROR function is used here.]*

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

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

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

### 4. Using the Filter function

As the name suggests, the **FILTER** function filters a range of cells based on certain criteria. You don’t write formulas with multiple functions for this method. Only the **FILTER **function is enough for performing the operation.

*Note: As of writing this article, the FILTER function is only available on Excel 365. So, if you are using other versions of Excel then you should check the other methods.*

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:

`=FILTER(E14:E34,(B14:B34=B8)*(C14:C34=B9)*(D14:D34=B10),"No Match")`

*Breakdown:*

*The multiplication operation:*

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

*=> (Product Column = Shirt)*(Color Column = Indigo)*(Size Column = L)*

=>{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 FILTER function:*

`FILTER(E14:E34,{0;0;0;0;0;0;0;0;0;0;0;0;1;0;0;0;0;0;0;0;0},"No Match")`

*=> FILTER(Price Column,{0;0;0;0;0;0;0;0;0;0;0;0;1;0;0;0;0;0;0;0;0},”No Match”)*

*=> 50*

*[The FILTER function searches the Price column with index numbers and returns the cell value where the corresponding index value is one (1), In this case, 50.]*

## Conclusion

I have narrowed and broken down five formulas to find cells with multiple criteria from three different arrays 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.