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.

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.

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.

• At this time, select cell D5 and insert the following formula.

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

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.

• Secondly, go to cell D5 and enter the following formula.

• Then, press the ENTER key to get the result.

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

Similar Readings

• Excel INDEX MATCH If Cell Contains Text
• INDEX MATCH with 3 Criteria in Excel (4 Examples)
• Excel INDEX MATCH to Return Multiple Values in One Cell
• INDEX MATCH Multiple Criteria with Wildcard in Excel (A Complete Guide)
• [Fixed!] INDEX MATCH Not Returning Correct Value in Excel (5 Reasons)

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

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.

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

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

• After that, navigate to the “Dataset2” sheet.

• Later, select the table_array which is the C5:G19 range in the Dataset2 worksheet.

• Next, complete the full formula like the following.

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

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

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.

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

• INDEX MATCH vs VLOOKUP Function (9 Examples)
• INDEX+MATCH with Duplicate Values in Excel (3 Quick Methods)
• How to Use INDEX-MATCH Formula in Excel to Generate Multiple Results
• INDEX MATCH across Multiple Sheets in Excel (With Alternative)
• Sum with INDEX-MATCH Functions under Multiple Criteria in Excel
• Index Match Sum Multiple Rows in Excel (3 Ways)
• How to Match Multiple Criteria from Different Arrays in Excel