You may sometimes find yourself in a situation When you are working with a large array of data, and you need to find some unique values or texts but don’t have a specific identifier for this purpose. In this case, vertical lookup 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 for value with multiple criteria in different sheets and return the result in another worksheet. Today, in this article we will learn some approaches to use the** INDEX MATCH** formula with multiple criteria in different sheets.

## Download Practice Workbook

Download this practice sheet to practice while you are reading this article.

## INDEX MATCH with Multiple Criteria in Different Sheet in Excel (2 Approaches)

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 look up data with multiple criteria in different sheets.

### 1. INDEX MATCH with Multiple Criteria for Columns in Different Sheet

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. You can do both Array or Non-Array ways.

#### i. Using the Array formula

**Step-1:**

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

**Step-2:**

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

**Step-3:**

Now apply the INDEX MATCH formula in cell** “D4”**.

The generic **INDEX MATCH** with multiple criteria is

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

Where:

**Return_range**is the range from which the value will be returned.**Criteria1, criteria2, …**are the conditions to be satisfied.**Range1, range2, …**are the ranges on which the required criteria should be searched.

Now insert the values into the formula. The final formula will look like,

**=INDEX(Data!$D$4:$D$14,MATCH(1,(‘M01’!B4=Data!$B$4:$B$14)*(‘M01’!C4=Data!$C$4:$C$14),0))**

Here,

**Return_range**is**Data!$D$4:$D$14.**Click on the**“Data”**worksheet and select the data range.**Criteria1**is**‘M01’!B4 (M-01).****Criteria2**is**‘M01’!C4 (Tom).****Range1**is**Data!$B$4:$B$14.**Click on the**“Data”**worksheet and select the**“ID”****Range2**is**Data!$C$4:$C$14.**Click on the**“Data”**worksheet and select the**“First Name”****Lookup_value**for the**“MATCH”**function is**“1”**because the function returns the relative position of the row for which all the criteria are**TRUE (1)**. If there are several**“1”**in the array, the position of the first value is returned.**Match_Type**is**“0”.**

The** INDEX MATCH** formula is an array formula. So, press **“CTRL+SHIFT+ENTER”** to get the result.

**Step-4:**

Now, apply the same formula for the rest of the cells.

#### ii. Without Using the Array formula

We can do the previous example without using the array formula. Let’s see how.

**Step-1:**

Make a table in a new worksheet just like the previous example.

**Step-2:**

In cell **“D4”** apply the **INDEX MATCH** formula with multiple criteria. We will use the data from a different worksheet (Data). The generic non-array **INDEX MATCH** formula is,

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

Insert the values into the formula. The final formula is,

**=INDEX(Data!$D$4:$D$14,MATCH(1,INDEX((‘M02’!B4=Data!$B$4:$B$14)*(‘M02’!C4=Data!$C$4:$C$14),0,1),0))**

Where,

**Return_range**is**Data!$D$4:$D$14.**Click on the**“Data”**worksheet and select the data range.**Criteria1**is**‘M01’!B4 (L-02).****Criteria2**is**‘M01’!C4 (Rose).****Range1**is**Data!$B$4:$B$14.**Click on the**“Data”**worksheet and select the**“ID”****Range2**is**Data!$C$4:$C$14.**Click on the**“Data”**worksheet and select the**“First Name”****Lookup_value**for the**“MATCH”**function is**“1”.****Match_Type**is**“0”.**

Press **“Enter”** to get the result.

**Step-3:**

Apply the same formula for the remaining cells.

### 2. INDEX MATCH with Multiple Criteria for Rows and Columns in Different Sheet

One of the unique features of the **INDEX MATCH** formula is that this formula can simultaneously lookup for values in both rows and columns in different sheets. Now we will learn this.

**Step-1:**

Let’s consider that your boss now gave you a dataset where **“Name”**,** “ID”** of some sales reps are given and their salary of the months **“Jan”, “Mar”, “May”, “Jul”** and** “Sep”** are given. Name this worksheet **“Data Range”**. Now you have to find the salary for some given criteria in a different sheet.

**Step-2:**

Create another table in a different sheet containing the columns **“Name”, “ID”, “Month”** where the criteria are given. Name this sheet **“M03”**. You have to retrieve the **“Sale”** using the given criteria.

**Step-3:**

Now apply the **INDEX MATCH **formula with multiple criteria in a different sheet. The format of this formula is

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

Insert the value into this formula. The final formula for our different sheets is

**=INDEX(‘Data range’!$C$5:$G$19,MATCH(‘M03′!B4,’Data range’!$B$5:$B$10,0),MATCH(‘M03′!C4&’M03′!D4,’Data range’!$C$3:$G$3&’Data range’!$C$4:$G$4,0))**

Where,

**Table_array**is**‘Data range’!$C$5:$G$19.**Click on the**“Data Range”**sheet.

And select the data.

**Vlookup_value**is**‘M03’!B4(K-01).****Lookup_column**is**‘Data range’!$B$5:$B$10.**Click on the “Data Range” column to select.**Hlookup_value1**is**‘M03’!C4(Tom).****Hlookup_value2**is**‘M03’!D4(May).****Lookup_row1**is**‘Data range’!$C$3:$G$3.****Lookup_row2**is**‘Data range’!$C$4:$G$4.****Match_Type**is**“0”.**

The selected Columns and rows are,

**Step-4:**

Now apply this formula by pressing **“CTRL+SHIFT+ENTER”** simultaneously and get the result.

**Step-5:**

Now since our formula for different sheets is working fine, apply this formula for the rest of the cells.

## Quick Notes

⏩ The** INDEX MATCH** is 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 **“BLOCK”** the data range using **“Absolute Cell Reference ($)”**.

## Conclusion

The **INDEX MATCH** formula is more dynamic than the vertical lookup or horizontal lookup function. This formula is extremely efficient when you use this formula for multiple criteria in different sheets. Hope the methods we discussed today prove useful to you. If you have any kinds of thoughts or confusion regarding this article, you are welcome to comment.

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 ?