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.

**Watch Video – Match Multiple Criteria from Different Arrays in Excel**

**Table of Contents**Expand

**How to Match Multiple Criteria from Different Arrays in Excel: 6 Methods**

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.

**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")`

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

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

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

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 look up values horizontally with multiple criteria 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))`

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 with multiple criteria.

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

- 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 rows and columns 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.

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

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

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

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

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

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

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

**<< Go Back to Multiple Criteria | INDEX MATCH | Formula List | Learn Excel**

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

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