How to Match Multiple Criteria from Different Arrays in Excel

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.

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.

The dataset

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.

Entering the array formula

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.

Entering the non-array formula

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.

Entering the COUNTIFS formula (AND Logic)

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

Entering the COUNTIFS formula (OR Logic)

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.

Entering the FILTER function

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.

Chinmoy Mondol

Greetings! Thank you for visiting my profile. I am Chinmoy Mondol. I am a conscientious, tech enthusiast individual with a voracious appetite for knowledge and a desire to learn more. I graduated from American International University-Bangladesh with a Bachelor's Degree in Computer Science and Engineering. I enjoy using my skills to contribute to the exciting technological advances that happen every day. Constant advancement and personal development are my guiding principles.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo