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.
Read More: How to Use INDEX MATCH with Multiple Criteria in Excel
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.
- 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.
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))
- 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.
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 Use INDEX-MATCH Function for Multiple Results in Excel
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.
=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))
- 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.
Similar Readings
- Excel INDEX MATCH with Multiple Criteria and Multiple Results
- Excel INDEX MATCH If Cell Contains Text
- INDEX MATCH with 3 Criteria in Excel
- Excel INDEX MATCH to Return Multiple Values in One Cell
- INDEX MATCH Multiple Criteria with Wildcard in Excel
- [Fixed!] INDEX MATCH Not Returning Correct Value in Excel
- Use INDEX MATCH for Multiple Criteria Without Array
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.
=INDEX(
- 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.
=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))
- 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.
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.
Read More: Excel INDEX MATCH Example
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.
=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.
Similar Readings
- How to Use INDEX MATCH with Multiple Criteria for Date Range.
- How to Match Multiple Criteria from Different Arrays in Excel
- INDEX-MATCH Formula to Find Minimum Value in Excel
- INDEX-MATCH with Multiple Matches in Excel
- How to Use INDIRECT, INDEX, and MATCH Functions in Excel
- INDEX, MATCH, and COUNTIF Functions with Multiple Criteria
- How to Use IF with INDEX & MATCH Functions in Excel
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.
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.
Further Readings
- How to Sum Multiple Rows Using INDEX MATCH Formula
- INDEX-MATCH with Duplicate Values in Excel
- Sum with INDEX-MATCH Functions under Multiple Criteria in Excel
- SUMIF with INDEX and MATCH Functions in Excel
- SUMPRODUCT with INDEX and MATCH Functions in Excel
- Excel INDEX-MATCH Formula to Return Multiple Values Horizontally
- INDEX Function to Match & Return Multiple Values Vertically in Excel
- INDEX MATCH across Multiple Sheets in Excel
- How to Use INDEX MATCH Instead of VLOOKUP in Excel
- XLOOKUP vs INDEX-MATCH in Excel
- How to Use Excel VBA INDEX MATCH with Array
- VBA INDEX MATCH Based on Multiple Criteria in Excel
- INDEX, MATCH and MAX with Multiple Criteria in 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.