How to Use INDEX MATCH with Multiple Criteria in a Different Sheet (2 Ways)

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,

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,

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

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.