When working with a large array of data, you may find yourself in a situation where you need to find some unique values or texts but don’t have a specific identifier for this purpose. In this case, a vertical or horizontal lookup with several conditions is used to find the result. But instead of using these functions, expert users normally apply the **INDEX MATCH** combination. The combination of the **INDEX** and **MATCH** functions is superior to **VLOOKUP** or **HLOOKUP** in many ways. The **INDEX MATCH** formula can look up values with multiple criteria on different sheet and return the result in another worksheet. Today, in this article, we will learn some approaches to using the **INDEX-MATCH** formula with multiple criteria in different sheets.

## INDEX MATCH Formula with Multiple Criteria in Different Sheet in Excel: 2 Methods

The **INDEX-MATCH** formula is quite efficient when you are finding data with multiple criteria for both columns and rows in different sheets. There are two distinctive approaches to lookup data with multiple criteria in different sheets. So, let’s explore them one by one.

Here, we have used the *Microsoft Excel 365* version, you may use any other version according to your convenience.

### 1. INDEX MATCH Formula with Multiple Criteria for Columns Only

For clarification, we are going to use a **Monthly Sales Report** of a particular organization. This dataset includes the **ID**, **First Name**, and their respective **Sales** in columns **B**, **C**, and **D** correspondingly.

Consider a scenario in which your boss assigned you the task of calculating the **Sales **amounts of various sales representatives using this worksheet. You can do it easily by using the **INDEX-MATCH** formula. You can do it using either an array or a non-array formula. So, let’s see them in action.

#### 1.1 Using the Array Formula

In this case we have to find **Sales **for a specific ** ID** and a specific

**from a different worksheet. This worksheet is named “**

*First Name*

*Dataset**”*. Now, follow the steps below carefully.

- First, make a data range in a new worksheet containing columns
,*ID*, and*First Name*. In this new worksheet, we will find the result in the*Sales***D5:D7**range. Name this worksheet as.*Array*

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

The generic **INDEX-MATCH** formula with multiple criteria is like the following.

**=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*- At this time, 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**.

- After that, press
**ENTER**.

*Note:** As this is an array formula, make sure you 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*.

- Currently, bring the cursor to the right-bottom corner of cell
**D5**. Actually, it’s the**Fill Handle**tool. - Consequently, double-click on this.

- As a result, it copies the formula to the following cells, and you’ll get results in those cells also.

#### 1.2 Without Using the Array Formula

We can do the earlier case without using the array formula. Let’s see how.

**📌**** Steps:**

- Firstly, make a table like the previous example.

Here, we’ll use the non-array **INDEX-MATCH** formula. Let’s see its generic form first.

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

- Secondly, go to cell
**D5**and 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**.

- Then, press the
**ENTER**key to get the result.

### 2. INDEX MATCH Formula with Multiple Criteria for Rows and Columns

One of the unique features of the **INDEX-MATCH** formula is that it can simultaneously lookup values in both rows and columns in different sheets. We’ll find out now.

Let’s consider that your boss has just given you a dataset where ** Name**,

**of some sales with**

*ID***of the months**

*Sales***,**

*Jan***,**

*Mar***,**

*May***and**

*Jul***are given. The name of this worksheet is “**

*Sep***Dataset2**”.

Presently, you have to find the ** Sales** for some given criteria in a different sheet. Let’s follow us.

**📌**** Steps:**

- Initially, construct another table in a different sheet containing the columns
,*Name*,*ID**an**d*where the criteria are given. Then, name this sheet*Month*. You have to retrieve the*Row-Column*using the given criteria.*Sales*

Consequently, we have to apply the **INDEX-MATCH** formula with multiple criteria in this sheet. The format of this formula is like the following.

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

- Then, go to cell
**E5**and call**the INDEX function**.

`=INDEX(`

- After that, navigate to the “
**Dataset2**” sheet.

- Later, select the
which is the*table_array***C5:G19**range in theworksheet.*Dataset2*

- Next, complete the full formula like the following.

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

Therefore, we can see the selected rows and columns in the image below.

- Finally, press
**ENTER**.

- Additionally, use the
**Fill Handle**tool to get complete results in the lower cells in the column.

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

We can extract the price of a certain product on a specific date.

Here, we have a list of products with their beginning and ending periods and their corresponding unit price.

Suppose we want to see the price of an ** Ice Cream** on

**. If the given date falls within the offered period of time, we’ll have the price extracted in any blank cell. So, without further delay, let’s see how to use**

*02-10-22 (month-day-year)***INDEX MATCH**with multiple criteria for date range.

**📌**** Steps:**

- Firstly, build an output range in the
**D19:D21**range. Here, we opt to find it for*3*products. You can customize it according to your needs.

- Secondarily, go to cell
**D19**and 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))`

- After that, hit
**ENTER**.

We can see a **#N/A error** in cell **D21** because the date in cell **C21** doesn’t lie within the described period in the dataset.

## Smart Alternative of INDEX MATCH with Multiple Criteria

If you are a user of *Office 365*, only then you are eligible to get the benefit of this function. Now, we’ll use **the FILTER function** to do the same job. So, to make it happen, adhere to the guidelines below.

**📌**** Steps:**

- Firstly, create a worksheet like
**Method 1**. - Then, select cell
**D5**and insert the following formula.

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

Thus, this formula is easier to apply and understand than the previous ones.

- Secondly, press the
**ENTER**key.

## Quick Notes

⏩ The **INDEX MATCH** is normally an array formula. So, you have to 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, remember to freeze the data range using an absolute cell reference (**$**). Simply press **F4** to apply it to the formula.

**Download Practice Workbook**

You may download the following Excel workbooks for better understanding and practice yourself.

## Conclusion

This article explains how to use **INDEX MATCH** with multiple criteria in different sheet in Excel in a simple and concise manner. Additionally, don’t forget to download the *Practice* file. Thank you for reading this article. We hope this was helpful. Please let us know in the comment section if you have any queries or suggestions.

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