How to Match Multiple Criteria from Different Arrays in Excel

You have come to the right place if you are looking for the answer or some unique tips to match multiple criteria from different arrays in Excel. There are various ways to match multiple criteria from different arrays in Excel. This article will walk you through each and every step with appropriate examples. As a result, you can use them easily for your purpose. Let’s move on to the article’s main discussion.


Download Practice Workbook

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


6 Methods to Match Multiple Criteria from Different Arrays in Excel

In this section, I will show you 6 quick and easy methods to match multiple criteria from different arrays in Excel on the Windows operating system. This article contains detailed explanations with clear illustrations for everything. I have used the Microsoft 365 version here. However, you may use any other version depending on your availability. Please leave a comment if any part of this article does not work in your version.

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.

Sample dataset to Match Multiple Criteria from Different Arrays in Excel


1. Using Array Formula with INDEX and MATCH Functions

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

📌 Steps:

  • For this, first insert the product name, color and size in cells G5, G6, G7
  • Then, insert the following formula into cell G8 to get the price for the product meeting those criteria:

=IFERROR(INDEX(E5:E20,MATCH(1,(G5=B5:B20)*(G6=C5:C20)*(G7=D5:D20),0)),"No Match")

Using INDEX and MATCH functions Match Multiple Criteria from Different Arrays in Excel

🔎 Formula Breakdown:

The Multiplication Operation:

→ (G5=B5:B20)*(G6=C5:C20)*(G7=D5:D20) = (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}*(G6=C5:C20)*(G7=D5:D20)}

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}

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 Function Operation:

→ MATCH(1,(0;0;0;0;0;0;0;0;0;0;0;0;1;0;0;0),0)) → 13

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

INDEX Function Operation:

→ IFERROR(INDEX(E5:E20,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.

Read More: Excel INDEX MATCH with Multiple Criteria (4 Suitable Examples)


2. Using a Non-Array Formula of INDEX and MATCH Functions

Here, I have tried to perform the same task as before. The formula is also the same except there is an extra INDEX function and a selected non-array type of the INDEX function.

📌 Steps:

  • For this, first insert the product name, color, and size in respective cells.
  • Then, insert the following formula into cell G8 to get the price for the product that meets those criteria:

=IFERROR(INDEX(E5:E25,MATCH(1,INDEX((G5=B5:B25)*(G6=C5:C25)*(G7=D5:D25),0,1),0)),"No Match")

🔎 Formula Explanation:

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.

Using a Non-Array Formula of INDEX and MATCH Functions

Read More: INDEX MATCH Multiple Criteria in Excel (Without Array Formula)


Similar Readings


3. INDEX MATCH Formula for Multiple Criteria from Different Horizontal and Vertical Arrays in Excel

3.1 Lookup Vertically in Columns

Apart from the previous ways described above, you can combine INDEX and MATCH functions to lookup for horizontal and vertical lookup with multiple criteria. Follow the steps below to do this.

📌 Steps:

  • Initially, click on cell C18 and insert the following formula.

=INDEX(D5:D14,MATCH(1,(B5:B14=C16)*(C5:C14=C17),0))

  • Subsequently, hit the Enter

INDEX MATCH Formula for Multiple Criteria in Vertically for Columns in Excel

As a result, you can get the desired result for your desired salesperson.


3.2 Lookup Horizontally in Rows

You can also combine the INDEX and MATCH functions to lookup for multiple criteria horizontally by following the steps below.

📌 Steps:

  • First, click on cell C10.
  • Subsequently, insert the following formula and press the Enter

=INDEX(C6:L6,MATCH(1,(C4:L4=C8)*(C5:L5=C9),0))

INDEX MATCH Formula for Multiple Criteria in Horizontally for Rows in Excel

Thus, you can get the desired person’s department by horizontal lookup.


4. INDEX MATCH Formula to Match Multiple Criteria from Arrays in Different Excel Sheets

Consider a situation where you are working on a business farm. Your boss gave you an assignment in which you have to find the sales amount of different sales reps from another worksheet. You can do it easily by using the INDEX MATCH formula.

  • In the following example, the “ID”, “First Name”, and “Sale” of workers are given arbitrarily. You have to find the “Sale” for a specific “ID” and a specific “First Name” in a different worksheet. The worksheet is named “Data”.

  • Make another table in a new worksheet containing columns “ID”, “First Name”, and “Sale”. In this new worksheet, we will find the result. Name this worksheet as “M01”.
  • Then, insert the following formula in cell D5 of “M01” worksheet.

=INDEX(Data!$D$5:$D$15,MATCH(1,('M01'!B5=Data!$B$5:$B$15)*('M01'!C5=Data!$C$5:$C$15),0))

  • Now, apply the same formula for the rest of the cells.

INDEX MATCH Formula to Match Multiple Criteria from Different Excel Sheets

  • Thus, you have used multiple criteria to find out a value from different worksheets.

5. Using the COUNTIFS Function to Match Multiple Criteria from Different Arrays

We can use the COUNTIFS function to match multiple criteria in Excel. In that case, we have to use the AND logic for criteria in different columns and OR logic for criteria in the same column.

5.1 Using AND Logic for Multiple Criteria in Multiple Columns

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.

📌 Steps:

  • First, insert the name of the product, color, and size in respective cells of range F5:F7.
  • Then, insert the following formula in cell F8 to get the count of the cells that match the given criteria:

=COUNTIFS(B5:B20,F5,C5:C20,F6,D5:D20,F7)

🔎 Formula Breakdown:

=COUNTIFS(B5:B20,F5,C5:C20,F6,D5:D20,F7) → COUNTIFS(Product Column, Shirt, Color Column, Indigo, Size Column, L) → 1

  • It searches for the values in the respective columns and increases the count if all the criteria are matched.
  • There is only one column where all the criteria match. So, it is the desired output.

Using the COUNTIFS Function to Match Multiple Criteria from Different Arrays in Excel

  • Thus, you have the cells count matching the criteria given for different arrays.

Read More: Index Match Multiple Criteria in Rows and Columns in Excel


5.2 OR Logic for Multiple Criteria in the Same Column

OR logic means that if one criterion matches, the TRUE value will be returned. Here, I have calculated the total number of rows where the color values are “Red” and “Yellow”.

To do that, insert the following formula in cell F4 to get the count of the cells that match the given criteria:

=SUM(COUNTIFS(C5:C20,{"Red","Yellow"}))

🔎 Formula Breakdown:

→ SUM(COUNTIFS(C11:C31,{“Red”,“Yellow”})) → SUM(COUNTIFS(Color column,{“Red”, ”Yellow”}))

COUNTIFS function searches for the values in the respective column and increases the count if any criteria are matched. As there are three “Red” and three “Yellow”, that’s why the COUNTIFS function returns 3,3.

→ SUM(3,3) → 6

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

OR Logic in COUNTIFS function for Multiple Criteria in the Same Column

  • Thus I have calculated total amount of red and yellow products.

Read More: Sum with INDEX-MATCH Functions under Multiple Criteria in Excel


6. 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. Here, I have fetched the Price of the Product (Cell B11) based on the product’s Name, Color, and Size.

📌 Steps:

  • First, insert the name of the product, color, and size in respective cells of range F5:F7.
  • Then, insert the following formula in cell F8 to get the price of the product that matches all the criteria.

=FILTER(E5:E20,(B5:B20=G5)*(C5:C20=G6)*(D5:D20=G7),"No Match")

🔎 Formula Breakdown:

The Multiplication Operation:

→ (B5:B20=G5)*(C5:C20=G6)*(D5:D20=G7) = (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}*(C5:C20=G6)*(D5:D20=G7)}

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}

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(PriceColumn {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.

Using the FILTER Function to Match Multiple Criteria from Different Arrays in Excel

  • Thus, I have calculated the price of a product that matches the multiple criteria given.

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.


Conclusion

In this article, you have found how to match multiple criteria from different arrays in Excel. 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

Chinmoy Mondol

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.

2 Comments
  1. him thanks for the wonderful tips! The formula keeps showing that there’s no match “1”.I’ve tried many different formula and it keeps showing the same warning. Could you please assist me? much appreciated!!

    • Reply
      Fahim Shahriyar Dipto Nov 23, 2022 at 11:16 AM

      Hello Avery,
      First of all, we would like to thank you for your appreciation. Again, thanks for taking the time to leave a comment about a problem that you’re facing. In our Excel file, the formula is working smoothly. You can follow the step-wise procedure thoroughly. If the problem in your file remains the same, then send the file to us. It will be easier for us to pinpoint the problem. We, the Exceldemy team, are always ready to solve users’ issues.
      Regards,
      Fahim Shahriyar Dipto
      Excel and VBA Content Developer

Leave a reply

ExcelDemy
Logo