INDEX MATCH Formula with Multiple Criteria in Different Sheet

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.


Download Practice Workbook

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


2 Methods to Apply INDEX MATCH Formula with Multiple Criteria in Different Sheet in Excel

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.

index match multiple criteria different sheet

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 First Name from a different worksheet. This worksheet is named “Dataset. Now, follow the steps below carefully.

📌 Steps:

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

Using the Array INDEX MATCH Formula

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))
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.
  • 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))
Here,
  • return_range is Dataset!$D$5:$D$15. Click on the Dataset worksheet and select the data range.
  • criteria1 is Array!B5 (M-01).
  • criteria2 is Array!C5 (Tom).
  • range1 is Dataset!$B$5:$B$15. Click on the Dataset worksheet and select the ID column.
  • range2 is Dataset!$C$5:$C$15. Click on the Dataset worksheet and select the First Name column.
  • lookup_value for 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.
  • match_type is 0.
  • After that, press ENTER.

using array index match formula with multiple criteria in different sheet

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.

Using Fill Handle

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

Read More: How to Select Specific Data in Excel (6 Methods)


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))
Where,
  • return_range is Dataset!$D$5:$D$15. Click on the Dataset worksheet and select the data range.
  • criteria1 is ‘Non Array’!B5 (L-02).
  • criteria2 is ‘Non Array’!C5 (Rose).
  • range1 is Dataset!$B$5:$B$15. Click on the Dataset worksheet and select the ID column.
  • range2 is Dataset!$C$5:$C$15. Click on the Dataset worksheet and select the First Name column.
  • lookup_value for the MATCH function is 1.
  • match_type is 0.
  • Then, press the ENTER key to get the result.

Without Using the Array Formula

Read More: Excel Index Match single/multiple criteria with single/multiple results


Similar Readings


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, ID of some sales with Sales of the months Jan, Mar, May, Jul and Sep are given. The name of this worksheet is “Dataset2”.

INDEX MATCH Formula with Multiple Criteria for Rows and Columns

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, Month where the criteria are given. Then, name this sheet Row-Column. You have to retrieve the Sales using the given criteria.

Getting Result for Multiple criteria for row and column

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 table_array which is the C5:G19 range in the Dataset2 worksheet.

Table array of INDEX function

  • 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))
Where,
  • vlookup_value is ‘Row-Column’!B5 (K-01).lookup_column is Dataset2!$B$6:$B$11.
  • hlookup_value1 is ‘Row-Column’!C5 (Tom).
  • hlookup_value2 is ‘Row-Column’!D5 (May).
  • lookup_row1 is Dataset2!$C$4:$G$4.
  • lookup_row2 is Dataset2!$C$5:$G$5.
  • match_type is 0.

INDEX MATCH array formula for multiple criteria

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

lookup rows and column

  • Finally, press ENTER.

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

using Fill Handle


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.

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

Suppose we want to see the price of an Ice Cream on 02-10-22 (month-day-year). 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 do it.

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

If you want to know more techniques and examples regarding this topic, you may read the article How to Use INDEX MATCH with Multiple Criteria for Date Range.


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. For an explanation, go to the INDEX MATCH with Multiple Criteria article.

  • Secondly, press the ENTER key.

Smart Alternative of INDEX MATCH with Multiple Criteria


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.


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. Please visit our website, Exceldemy, a one-stop Excel solution provider, to explore more.


Further Readings

Asikul Himel
2 Comments
  1. 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 ?

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

Leave a reply

ExcelDemy
Logo