Excel is a useful tool for matching multiple criteria from different arrays. Matching criteria is one of the most common things that people do with Excel every day. You can match multiple different criteria from a range of different arrays using different ways. We’ll show you how to match multiple different criteria from different arrays in Excel today with examples. I’m making examples and workbooks with **Microsoft 365**. You can also use the version you prefer.

## 📁 Download Excel File

To prepare this article, we used an Excel workbook, which is available for download. Additionally, you can edit and customize the data you enter while viewing the results.

## Learn to Match Multiple Criteria from Different Arrays in Excel with These 6 Unique Methods

Let’s get to the main point of the article. In this tutorial, I’ll show you six handy ways to match using multiple criteria from many different arrays in Excel. Everything is explained in detail and shown with clear pictures in this article. I’ve been using Microsoft 365 here. But you can use other versions if you like. I tried to give easy examples within this dataset. There is information in the dataset about some items and their sales. As we can observe in the image below, it has columns with headings: Region, Month, Item, and Sales. Using the information on the region, month, and item, we will find the sales.

**Method 1**### 1. Using MATCH and INDEX Functions Without Array Formula

Here, I got the sales of the item by using the region, month, and item. We will use both the **INDEX **and **MATCH**. Within a given table or range, **INDEX **function returns either a value or a reference to a value. The **MATCH **function looks for a given item in a range of cells and then returns the relative position of that item within the range.

**⬇️⬇️ STEPS ⬇️⬇️**

- First, put the region, month, and item in the respective cells.
- Now put this formula in cell
**H9**to know about prices for the item that meets these criteria:

**=IFERROR(INDEX(E6:E25,MATCH(1,INDEX((H6=B6:B25)*(H7=C6:C25)*(H8=D6:D25),0,1),0)), “No Match”)**

- Then we press
**Enter**. We will get the result in cell**H9**.

**🔨 Formula Breakdown**

`👉`

The operation of multiplication will look for the values in each column and either return **TRUE **or **FALSE **based on what it finds. The asterisk which is a **Multiplication Operator (*) **changes these values into only **0** and **1**. After that, it does the multiplication, which changes all values except the one you want into **0**.

`👉`

This function searches the converted range for **1 **(the value) and returns which position the value is in.

`👉`

This function gives back values in the 9th row of price column, which in turn, is the output that was asked for. **INDEX **function now will return the **#N/A** error if there are no matches. We used **IFERROR** here to handle these kinds of errors and show the message “**No Match**.”

**Method 2**

**2. Using MATCH and INDEX Functions with an Array Formula**

Here, I got the sales of the item by using the region, month, and product. We will use both **INDEX **and **MATCH** here. Within a given table or range, **INDEX **function usually return either a value or a reference to a value. The **MATCH **function looks for a given item in a range of cells and then returns the relative position of that item within the range.

**⬇️⬇️ STEPS ⬇️⬇️**

- First, put the region, month, and item in the respective cells.
- After that, type the formula inside cell
**H9**to get price of the product that meets the criteria we desire:

**=IFERROR(INDEX(E6:E21,MATCH(1,(H6=B6:B21)*(H7=C6:C21)*(H8=D6:D21),0)),”No Match”)**

- Then we press
**Enter**. We will get the result in cell**H9**.

**🔨 Formula Breakdown**

`👉`

The special multiplication operation will look for the values in each column and then gives **TRUE **or **FALSE **based on what it finds. The **Multiplication Operator** (*****) changes these values into **0**s and **1**s, and then it does the multiplication, which changes all values except the one you want into **0**.

`👉`

The next function returns the position after searching the converted range for **1**.

`👉`

This function gives back the value. The **INDEX **function here shows the **#N/A** error if there are no matches. Moreover, the **IFERROR **function handles these types of errors and show the message “**No Match**.”

**Method 3**### 3. Utilizing INDEX-MATCH Functions to Match in Different Worksheets

Suppose, you need to use another worksheet to figure out how much each sale is made. Using the **INDEX**–**MATCH** formula is a simple way to do it. The **INDEX-MATCH**** **formula combines the **INDEX **with **MATCH **in Excel. Within a given table or range, the **INDEX **function generally returns either a value or a reference to a value. The **MATCH **function looks for a given item in a range of cells and then returns the relative position of that item within the range.

**⬇️⬇️ STEPS ⬇️⬇️**

- In the next example, “Region,” “Month,” and “Item” are given at random. On a different worksheet, we have to first find the “Item” for a certain “Month” and “Region.”

- In a new worksheet, make a new table with the columns “Region,” Month,” and “Item.” We will look for the result in this new worksheet.
- In cell
**D6**of the new worksheet, type this formula:

**=INDEX(Data!$D$5:$D$15,MATCH(1,(‘Different sheet’!B6=Data!$B$5:$B$15)*(‘Different sheet’!C6=Data!$C$5:$C$15),0))**

- Let’s copy the formula down. That’s how we find the results.

**🔨 Formula Breakdown**

`👉`

**MATCH **function looks for the match from the previously selected sheet.

`👉`

The next function searches the converted range for **1** as the value. The function returns the position.

`👉`

This function gives back values in the 9th row of price column. **INDEX **function here will return the **#N/A** error if there are no matches. We used **IFERROR **here to handle these kinds of errors and show the message “No Match.”

**📕 Read More: 3 Easy Ways to Use INDEX-MATCH Across Multiple Sheets**

**Method 4**### 4. Applying INDEX-MATCH Functions for Vertical and Horizontal Matches

You can also use a combination of the **INDEX **with **MATCH** to do horizontal and vertical lookups with more than one criterion. We will use the **INDEX-****MATCH **combination. Within a given table or range, the **INDEX **function generally returns either a value or a reference to a value. The **MATCH **function looks for a given item in a range of cells and then returns the relative position of that item within the range. To do this, follow the steps below:

**Sample title**

#### 4.1 Search Vertically

**⬇️⬇️ STEPS ⬇️⬇️**

- First, select cell
**G7**and type:

**=INDEX(D6:D15,MATCH(1,(B6:B15=G5)*(C6:C15=G6),0))**

- Then press
**Enter**to see the result.

**Match Multiple Criteria**#### 4.2 Search Horizontally

**⬇️⬇️ STEPS ⬇️⬇️**

- First, select cell
**G11**and type:

**=INDEX(C7:L7,MATCH(1,(C5:L5=G9)*(C6:L6=G10),0))**

- Then press
**Enter**to see the result.

**🔨 Formula Breakdown**

`👉`

**MATCH **function looks for the match from the previously selected sheet.

`👉`

The next function searches the converted range for **1** and gives us the position.

`👉`

This function gives back values in the 9th row of price column, which in turn, is the output that was asked for. The **INDEX **function here will return the **#N/A** error if there are no matches. We used **IFERROR **here to handle these kinds of errors and show the message “No Match.”

**📕 Read More: Use INDEX Function to Match and Return Multiple Values Vertically**

**Method 5**### 5. Matching Criteria with COUNTIFS Function

We can match multiple different criteria in Excel by using the **COUNTIFS function**. We use the statistical function **COUNTIF **to count how many cells meet a certain condition. Here, we are going to to use the **AND** logic to match varying criteria in varying columns and the **OR** logic for matching those criteria within the same column.

**COUNTIFS Function**#### 5.1 Combining with AND Logic

We use the statistical function **COUNTIF **to count how many cells meet a certain condition along with **AND **logic.

**⬇️⬇️ STEPS ⬇️⬇️**

- First, put the item’s region, month, and item in cells in the range
**G6:G8**. - Then, put this formula into cell
**G9.**

**=COUNTIFS(B6:B21,G6,C6:C21,G7,D6:D21,G8)**

- Then we press
**Enter**. So, it’s the desired result.

**
**

**🔨 Formula Breakdown**

`👉`

The formula looks for the specific values in each column and takes them into the count when all of the conditions are met.

`👉`

We can find one column only where all of the requirements are met. That is the result.

**COUNTIFS Function**#### 5.2 Combining with OR Logic

We use the statistical function **COUNTIF **to count how many cells meet a certain condition along with **OR **logic. The **SUM **function adds the two numbers together and gives back the result you want.

**⬇️⬇️ STEPS ⬇️⬇️**

- First, type the formula within cell
**G5**to find out how many cells meet the criteria:

**=SUM(COUNTIFS(D6:D21,{“Apple”,”Banana”}))**

- Then we press
**Enter**. So, it’s the desired result.

**🔨 Formula Breakdown**

`👉`

The formula looks for the specific values in each column and takes them into the count when all of the conditions are met. **COUNTIFS** returns 4,2 because there are four “Apple”s and two “Banana”s.

`👉`

The **SUM **portion adds two of the numbers together and gives back the result you want.

**📕 Read More: 10 Cases to SUM with INDEX-MATCH for Multiple Criteria in Excel**

## How to Use Filter Function in Excel

**FILTER **function sorts a group of cells according to certain rules. For this method, you need not write formulas along with many different functions. For the operation to be done, you only need **FILTER **function.

**⬇️⬇️ STEPS ⬇️⬇️**

- First, put the region, month, and item’s name in the respective cells.
- Next, type the below formula in cell
**H9**to know price of the item that meets those criteria:

**=FILTER(E6:E21,(B6:B21=H6)*(C6:C21=H7)*(D6:D21=H8),”No Match”)**

- Then we press
**Enter**. We will get the result in cell**H9**.

**🔨 Formula Breakdown**

`👉`

The special multiplication operation will look for the values in each column and finds **TRUE** or **FALSE** based on what it finds. Multiplication Operator (*) changes these values into either** 0**s or **1**s. After that, it does the multiplication, which changes all values except the one you want into 0.

`👉`

Next, the function searches the converted range for **1** and gives us the position as output.

`👉`

**FILTER **function looks for index numbers in the **Sales **column and gives back the value of the cell where the related index number is **1**. Here the value is 55.

## 📄 Important Notes

🖊️ In Excel, the ribbon is used to access various commands. You can change a lot of things in the options dialogue window, like the ribbon, formulas, proofing, saving, and so on. So the appearance of worksheets on your device could be different from ours.

🖊️ A practice workbook is given so that you can practice yourself.

🖊️ All these exercises and tutorials are done on Microsoft Office 365. Some functions may be unavailable in previous versions.

🖊️ At the end of the **Excel file**, there is a sheet where they can practice.

## 📝 Takeaways from This Article

`📌`

The reader will be able to match multiple different criteria for different arrays in Excel easily with various methods.

`📌`

Readers can use both Array and Non-array formulas to match different arrays in Excel.

`📌`

Readers can also use the **INDEX MATCH **formula for looking in rows and columns.

`📌`

Moreover, users can do this job for multiple sheets.

`📌`

They can easily apply the **COUNTIFS **function for this operation.

`📌`

Viewers can utilize **FILTER**.

`📌`

One will be able to match these multiple criteria from many different arrays in Excel easily with the quickest method.

## Conclusion

That concludes today’s session. These are the methods for matching multiple criteria from different arrays in Excel easily. We are hopeful that this article will be extremely beneficial to you. Don’t forget to leave your thoughts and questions in the comments section and explore our website, **ExcelDen**, the best Excel solutions provider.