## Dataset Overview

We’ll use a dataset that 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.

**Method 1 – Using Array Formula with INDEX and MATCH Functions**

**Scenario:** Fetch the **Price **of the **Product **(**Cell B11**) based on the product’s **Name**, **Color, **and **Size.**

** Steps**

- Insert the product
**name**,**color**, and**size**in cells**G5**,**G6**, and**G7**. - Then, enter the following formula in 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)}**

Checks each condition and returns TRUE/FALSE values.

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

**Method 2 – Using a Non-Array Formula of INDEX and MATCH Functions**

**Scenario:** Fetch the **Price **of the **Product **(**Cell B11**) based on the product’s **Name**, **Color, **and **Size.**

** Steps**

- Insert the product
**name**,**color**, and**size**in respective cells. - Enter the following formula in 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 new **INDEX** function converts the previous array formula to a non-array formula, making it easier for users unfamiliar with Excel array functions.

### Method 3 – INDEX MATCH Formula for Multiple Criteria from Different Horizontal and Vertical Arrays in Excel

#### 3.1 Lookup Vertically in Columns

** Steps**

- Click on cell
**C18**and insert the following formula:

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

This gives you the desired result for your desired salesperson.

- Press
**Enter**.

#### 3.2 Lookup Horizontally in Rows

** Steps**

- Click on cell
**C10**. - Insert the following formula and press
**Enter**:

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

This provides the desired person’s department through horizontal lookup.

### Method 4 – INDEX MATCH Formula to Match Multiple Criteria from Arrays in Different Excel Sheets

Imagine you’re working on a business farm, and your boss assigns you a task: find the sales amount of different sales reps from another worksheet.

In this example, we have arbitrary data for **ID**, **First Name**, and **Sale** of workers. The goal is to find the **Sale** for a specific **ID** and a specific **First Name** in a different worksheet named **Data**.

- Create another table in a new worksheet (let’s call it
**M01**) with columns**ID**,**First Name**, and**Sale**. - In cell
**D5**of the**M01**worksheet, enter the following formula:

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

The formula searches for the specified **ID** and **First Name** in the **Data** worksheet and retrieves the corresponding **Sale** value.

- Apply the same formula to the rest of the cells in the
**M01**worksheet.

**Method 5 – Using the COUNTIFS Function to Match Multiple Criteria from Different Arrays**

**5.1 Using AND Logic for Multiple Criteria in Multiple Columns**

**AND** logic requires all criteria to be matched. Let’s calculate the total number of rows based on the **Name**, **Color**, and **Size** criteria.

** Steps**

- Insert the product
**name**,**color**, and**size**in cells**F5**,**F6**, and**F7**. - Enter the following formula in cell
**F8**to count 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**

- The formula counts rows where all criteria (product name, color, and size) match.
- In this case, the result is 1 because there’s only one row that meets all criteria.

**5.2 OR Logic for Multiple Criteria in the Same Column**

**OR** logic means that if any criterion matches, the **TRUE** value is returned.

- To count the total number of rows where the color values are
**Red**or**Yellow**, enter the following formula in cell**F4**:

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

** Formula Breakdown**

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

- The
**COUNTIFS**function checks each row for color values**Red**or**Yellow**and returns**3**for each color. - The
**SUM**function adds these counts, resulting in a total of**6**rows.

**Method 6 – Using the FILTER Function**

The **FILTER **function lives up to its name by allowing you to filter a range of cells based on specific criteria. Unlike other methods that involve multiple functions, here we rely solely on the **FILTER** function. Let’s break down the steps:

** Steps**

- Enter the name of the
**product**,**color**, and**size**in the respective cells of the range**F5:F7**. - Insert the following formula into cell
**F8**to retrieve the**price**of the product that meets all the specified criteria:

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

** Formula Breakdown**

**Multiplication Operation:**

The expression **(B5:B20=G5)*(C5:C20=G6)*(D5:D20=G7)** evaluates to a series of **TRUE/FALSE** values.

For example, if the product column equals “Shirt,” the color column equals “Indigo,” and the size column equals “L,” the result would be:

**{FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; FALSE; FALSE}**

The **Multiplication Operator (*)** converts these values to **0s** and **1s**, effectively filtering out all other values except the desired output.

**FILTER Function:**

The **FILTER** function searches the Price column (E14:E34) using the index values from the previous step.

It returns the cell value where the corresponding index value is 1 (in this case, 50).

**Note: **

As of the time of writing this article, the **FILTER** function is only available in Excel 365. If you’re using other versions of Excel, be sure to explore alternative methods.

You can download the practice workbook from here:

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