### Method 1 – INDEX-MATCH Formula with Multiple Criteria for Columns Only

We’ll use a **Monthly Sales Report** of a particular organization, containing the **ID**, **First Name**, and their respective **Sales** in columns **B**, **C**, and **D,** respectively.

We’ll calculate the **Sales **amounts of various sales representatives using this worksheet.

#### Case 1.1 – Using an Array Formula

We’ll find **Sales **for a specific ** ID** and a specific

**from a different worksheet named “**

*First Name*

*Dataset**”*.

**Steps:**

- Make a data range in a new worksheet containing columns
,*ID*, and*First Name*in the*Sales***D5:D7**range. - Name this worksheet
.*Array*

Now we’ll apply the **INDEX-MATCH** formula to find the ** Sales** amount.

The generic **INDEX-MATCH** formula with multiple criteria is as follows:

**=INDEX(return_range, MATCH(1, (criteria1=range1) * (criteria2=range2) * (…), 0))**

**is the range from which the value will be returned.**

*return_range***,**

*criteria1***, … are the conditions to be satisfied.**

*criteria2***,**

*range1***, … are the ranges on which the required criteria should be searched.**

*range2*

- Select cell
**D5**and insert the following formula:

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

is*return_range***Dataset!$D$5:$D$15**. Click on theworksheet and select the data range.*Dataset*is*criteria1***Array!B5**().*M-01*is*criteria2***Array!C5**().*Tom*is*range1***Dataset!$B$5:$B$15**. Click on theworksheet and select the*Dataset*column.*ID*is*range2***Dataset!$C$5:$C$15**. Click on theworksheet and select the*Dataset*column.*First Name*for*lookup_value***the MATCH function**is**1**as it provides the relative location of the row for each of the conditions that are TRUE. The location of the first result is retrieved if there are several instances of 1 in the array.is*match_type***0**.

- Press
**ENTER**.

*Note:** As this is an array formula, press CTRL + SHIFT + ENTER instead of ENTER if you are using any version other than Excel 365. And don’t put those curly braces around the formula. Excel will automatically add them to the array formula*.

- Bring the cursor to the right-bottom corner of cell
**D5**to activate the**Fill Handle**tool. - Double-click on it.

The formula is copied to the cells below.

#### Case 1.2 – Without Using the Array Formula

**Steps:**

- Make a table like the previous example.

The generic form of the non-array **INDEX-MATCH** formula is as follows:

**INDEX(return_range, MATCH(1, INDEX((criteria1=range1) * (criteria2=range2) * (..), 0, 1), 0))**

- In cell
**D5**, enter the following formula:

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

is*return_range***Dataset!$D$5:$D$15**. Click on theworksheet and select the data range.*Dataset*is*criteria1***‘Non Array’!B5**().*L-02*is*criteria2***‘Non Array’!C5**().*Rose*is*range1***Dataset!$B$5:$B$15**. Click on theworksheet and select the*Dataset*column.*ID*is*range2***Dataset!$C$5:$C$15**. Click on theworksheet and select the*Dataset*column.*First Name*for the*lookup_value***MATCH**function is**1**.is*match_type***0**.

- Press
**ENTER**to return the result.

### Method 2 – INDEX-MATCH Formula with Multiple Criteria for Rows and Columns

Consider the following dataset where ** Name **and

**of some sales together with**

*ID***of the months**

*Sales***,**

*Jan***,**

*Mar***,**

*May***and**

*Jul***are given in a worksheet named “**

*Sep***Dataset2**”.

Let’s find the ** Sales** for some given criteria in a different sheet.

**Steps:**

- Construct another table in a different sheet containing the columns
,*Name*,*ID**an**d*where the criteria are given.*Month* - Name this sheet
.*Row-Column*

The format of the **INDEX-MATCH** formula that we will use is as follows:

**=INDEX(table_array, MATCH(vlookup_value, lookup_column, 0), MATCH(hlookup_value1 & hlookup_value2, lookup_row1 & lookup_row2, 0))**

- Go to cell
**E5**and call**the INDEX function**:

`=INDEX(`

- Navigate to the “
**Dataset2**” sheet.

- Select the
which is the*table_array***C5:G19**range in theworksheet.*Dataset2*

- Complete the full formula as follows:

`=INDEX(Dataset2!$C$6:$G$20,MATCH('Row-Column'!B5,Dataset2!$B$6:$B$11,0),MATCH('Row-Column'!C5&'Row-Column'!D5,Dataset2!$C$4:$G$4&Dataset2!$C$5:$G$5,0))`

is*vlookup_value***‘Row-Column’!B5**().*K-01*is*lookup_column***Dataset2!$B$6:$B$11**.is*hlookup_value1***‘Row-Column’!C5**().*Tom*is*hlookup_value2***‘Row-Column’!D5**().*May*is*lookup_row1***Dataset2!$C$4:$G$4**.is*lookup_row2***Dataset2!$C$5:$G$5**.is*match_type***0**.

The selected rows and columns are as in the image below.

- Press
**ENTER**.

- Use the
**Fill Handle**tool to return results in the lower cells in the column.

## How to Apply the INDEX-MATCH Formula with Multiple Criteria for a Date Range

We can extract the price of a certain product on a specific date. We have a list of products with their beginning and ending periods and their corresponding unit price.

We want to see the price of an ** Ice Cream** on

*02-10-22**(month-day-year)*. If the given date falls within the specified period, we’ll extract the price into a blank cell.

**Steps:**

- Build an output range in cells
**D19:D21**. We want to find the price for*3*products, but you can customize it according to your needs.

- In cell
**D19**, enter the following array formula:

`=INDEX($E$5:$E$16,MATCH(1,(($B$5:$B$16=B19)*($D$5:$D$16>=C19)*($C$5:$C$16<=C19)),0))`

- Press
**ENTER**.

A **#N/A error** is returned in cell **D21** because the date in cell **C21** doesn’t lie within the described period in the dataset.

## Smart Alternative to INDEX-MATCH with Multiple Criteria

If you are a user of *Office 365*, you can use **the FILTER function**.

**Steps:**

- Create a worksheet like in
**Method 1**. - In cell
**D5**, enter the following formula:

`=FILTER(Dataset!$D$5:$D$15,(Dataset!$B$5:$B$15=Alternative!B5)*(Dataset!$C$5:$C$15=Alternative!C5))`

This formula is easier to apply and understand than the previous ones.

- Press
**ENTER**.

## Quick Notes

- The
**INDEX-MATCH**is normally an array formula, so press**CTRL+SHIFT+ENTER**instead of**ENTER**to get the result. - If you want to apply the same formula for the rest of the cells, freeze the data range using an absolute cell reference (
**$**). Simply press**F4**to apply it to the formula.

**Download the Practice Workbook**

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

On step 3 forlmula on pis is like this:

=INDEX(‘Data range’!’Data range’!C5:G19 <<< double "Data range"?

other

array: 'Data range'!$B$5:$B$10 <<< from row 5 to 10 ?

Hi KALI,

Normally when we write down a formula that has references from multiple sheets, the sheet name appears first. That’s why “Data range” appears. You can check the final image of step 3 to get the final formula.

‘Data range’!’Data range’!C5:G19 is corrected now. Thank you for your observation.

‘Data range’!$B$5:$B$10 refers to the range B5:B10. The selected range starts from the 5th row to the 10th row of column B.

Thanks again. Have a good day.