In this article, we will demonstrate two quick methods to use the INDEX and MATCH functions in Excel to sort some data matching multiple criteria using a non-array formula. The INDEX and MATCH functions can be used together for both array and non-array based formulas. Here, we’ll focus on the use of a non-array formula.
The INDEX Function
Consider the dataset below. To find how much the 11th student obtained in Physics, we could manually count 1… 2… 3.. in the Student Name column, starting from Mitchel Robbins down to the 11th row, to identify Alisha Moor, who achieved a 34 in physics. But we can derive this more elegantly using Excel’s INDEX Function.
The INDEX function takes three arguments.
- A range of cells in which to find the datum. In our case it is B5:E24.
- The number of the row of the datum to find within the range of cells. As we want to know about the 11th student, the row number here would be 11.
- The number of the column of the datum to find within the range of cells. As here we want to know about the marks in Physics, the column number would be 3.
So, the syntax for the INDEX Function is:
=INDEX(range,row_number,column_number)
In our example, the formula will be:
=INDEX(B5:F24,11,3)
Insert this formula in any cell, and the datum of row 11 and column 3 from the range B5:E24, will be returned. In our case, it is 34 points achieved by the 11th student, Alisha Moor.
Points to Keep in Mind
- If you have only one row in the range, inserting the row number is optional. Excel will automatically take it as 1.
- Likewise, if you have only one column in the range, inserting the column number is optional. Excel will automatically take it as 1.
- If you enter any row number or column number that falls out of the range, Excel will return a reference error (#REF!).
- Also, if you enter 0 in place of the row number, Excel will return the whole column as output. But that will work like an array formula. So you would have to select multiple (the required number of) cells and press Ctrl+Shift+Enter instead of Enter.
- And if you enter 0 in place of the column number, Excel will return the whole row as output. That will work like an array formula too. So you would have to select multiple (the required number of) cells and press Ctrl+Shift +Enter.
This is the array form of the INDEX function. There is another form of the INDEX function, called the reference form, which is not necessary here.
The MATCH Function
The syntax of the MATCH function is:
=MATCH(lookup_value, lookup_array, match_type)
The MATCH function takes 3 arguments.
- A specific text or number.
- A range of cells.
- The Match type (-1, 0, 1). 0 is for an exact match.
It returns the position of the cell within the range that matches with the specific text or number provided. Going back to our dataset, if we select any cell and enter the formula:
=MATCH("Alisha Moor",B11:B20,0)
It will return 5 because Alisha Moor is in the 5th position in the range from B11 to B20.
Points to Keep in Mind:
- If the MATCH function does not find a match, it returns a Value not Available (#N/A!) error.
- The MATCH function does not distinguish between uppercase and lowercase letters.
Using INDEX MATCH for Multiple Criteria Without an Array: 2 Handy Approaches
Suppose we want to find the student obtaining a 100 mark in all three subjects. There are 3 criteria here.
- 100 in Mathematics, which is column B.
- 100 in Physics, which is column C.
- 100 in Chemistry, which is column D.
Let’s solve for one criterion first, the student with marks of 100 only in Mathematics. First, we’ll find the position of the cell in column C which contains 100 in the range C5 to C24 using the MATCH function. The formula will be:
=MATCH(100,C5:C24,0)
The answer will be 14, because in the 14th cell of column C, there is a 100.
Now we’ll use this value to find the name of the student. Using the INDEX function, we’ll return the 14th value in the Student Name column in the range B5 to B24.
The formula will be:
=INDEX(B5:B24,14,1)
And the output will be Ricky Ben.
So the complete formula will be:
=INDEX(B5:B24,MATCH(100,C5:C24,0),1)
Using this formula, we will get the same result, Ricky Ben.
We have completed the task with one criterion. But how can we do this for multiple criteria without using an array formula?
Method 1 – Using a Helper Column
This is the easier approach. First, we’ll merge the columns to which the criteria belong into a new column. Here, the three criteria are 100 in mathematics, 100 in physics, and 100 in chemistry, which are located in columns C, D, and E, respectively. We’ll combine them into a new column, F, using the CONCATENATE function.
Steps:
- To merge, either use this formula in the first cell of column F, F5:
=CONCATENATE(C5,",",D5,",",E5)
- Then, drag the Fill Handle to AutoFill the formula into the rest of the cells.
- Or, we can directly merge them into F5 by using the ampersand symbol (&), and then drag the Fill Handle.
=C5&","&D5&","&E5
In both cases, the numbers of the three subjects for each student will be merged into column F like this:
We can now consider the merged value as a single criterion and use the INDEX and MATCH formula for a single criterion shown above. In this case, the formula will be:
=INDEX(B5:B24,MATCH("100,100,100",F5:F24,0))
Ricky Ben will be returned, because he is the one with 100 in all three subjects.
Special Note: Within the MATCH function, we covered the first argument (lookup_value) between apostrophes (“”). Although they were numbers before being merged, after being merged they were converted to text. That’s why we had to use apostrophes (“”).
Method 2 – Using Nested INDEX and MATCH Functions
Another more complex approach is to use nested INDEX and MATCH functions to match multiple criteria at once.
Steps:
- Enter the following formula in cell G5 and press Enter:
=INDEX(B5:B24,MATCH(1,INDEX((C5:C24=100)*(D5:D24=100)*(E5:E24=100),0,1),0),1)
Formula Breakdown
- C5:C24=100
- Firstly, we compare 100 with each mark in Mathematics. An array of True or False Boolean values will be returned, True if any mark is equal to 100, and False otherwise.
- We repeat the process for the marks in Physics and Chemistry respectively.
- (C5:C24=100)*(D5:D24=100)*(E5:E24=100)
- Then we multiply the three values returned. The Boolean values will be converted to numbers (1 and 0) after multiplication. As anything multiplied by 0 is 0, after multiplication the resultant array will contain 1 only if there was 1 in all the cells of the row before multiplication.
- In other words, in the resultant array, only the position where marks in all 3 subjects are 100 will have a 1. All others will have 0.
- INDEX((C5:C24=100)*(D5:D24=100)*(E5:E24=100),0,1)
- We enter this resultant array within an INDEX function as the range, while keeping the row number as 0, and the column number as 1.
- As the row number is 0, the INDEX function will return the column with the number 1, which refers to the resultant array which we used as the range.
- MATCH(1,INDEX((C5:C24=100)*(D5:D24=100)*(E5:E24=100),0,1),0)
- We enter this number into a MATCH function and find out if there is any 1 within it.
- INDEX(B5:B24,MATCH(1,INDEX((C5:C24=100)*(D5:D24=100)*(E5:E24=100),0,1),0),1)
- Finally, we enter the number within another INDEX function as the row number, with the name of the students as its range and the column number as 1.
- This will return the Name of the Student in that position. And so, we will get the student with 100 in all three subjects, Ricky Ben.
We can extract any data from any dataset using this formula, maintaining multiple criteria. It is a non-array formula, meaning that we do not need to press Ctrl+Shift+Enter to enter this formula. Pressing Enter will do.
Comparison with an Array Formula
There is a comparatively shorter formula for using INDEX MATCH for multiple criteria, but this is an array formula. After entering it into the formula bar, you have to press Ctrl+Shift+Enter to enter it. Excel will automatically put curly braces ({}) around the formula in the formula bar, indicating that it’s an array formula. If you are using the Microsoft 365 version, you can simply press Enter and it will not show a curly bracket.
The formula is:
=INDEX(B5:B24,MATCH(1,(C5:C24=100)*(D5:D24=100)*(E5:E24=100),0),1)
Using INDEX MATCH with Multiple Criteria for a Date Range
Suppose we want to extract the price for a certain product on a specific date. For example, we want to see the price of an Ice Cream on 02-10-22 (month-day-year). If the given date falls within the given period of time, we’ll extract the price into a blank cell.
Steps:
- Enter the following formula in cell D19:
=INDEX($E$5:$E$16,MATCH(1,(($B$5:$B$16=B19)*($D$5:$D$16>=C19)*($C$5:$C$16<=C19)),0))
- Press Enter if you are using Microsoft 365, or Ctrl+Shift+Enter for older versions.
Formula Breakdown
- $E$5:$E$16 refers to the array argument.
- Inside the MATCH function, $B$5:$B$16=B19, $D$5:$D$16>=C19, and $C$5:$C$16<=C19 declare the criteria.
- The MATCH function locates the position of a given value within a row, column, or table. Here, it passes the row number for the INDEX function.
- The MATCH portion of the formula assigns 1 as the lookup_value, ($B$5:$B$16=G5)*($D$5:$D$16>=H5)*($C$5:$C$16<=H5) as the lookup_array, and 0 declares the [match_type] as an exact match.
- Finally, the MATCH formula returns 3, as it finds Ice Cream in row number 3.
Download Practice Workbook
<< Go Back to Multiple Criteria | INDEX MATCH | Formula List | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
Filters on each column you can specify equal to less than or greater than super handy!
Thanks, James!
Thank you for this. I was looking to see how to use the non-array version in LibreOffice Calc as opposed to Excel and found that it works when you change the formula from (as you have written it):
=INDEX(B5:B24,MATCH(1,INDEX((C5:C24=100)*(D5:D24=100)*(E5:E24=100),0,1),0),1)
To:
=INDEX(B5:B24,MATCH(1,INDEX((C5:C24=100)*(D5:D24=100)*(E5:E24=100),0,0),0),1)
It worked. For some reason, the “1” just before the third to last parenthesis throws it off in Calc. It may be a bug in Calc for LO 7.4.
I checked the last Excel version I own (2010) and it worked with either the “1” or “0” in that space.
Paul, we appreciate your analysis. That 1 represents column_number, which is an optional argument. You can omit that in the original formula and it will still return Ricky Ben.
=INDEX(B5:B24,MATCH(1,INDEX((C5:C24=100)*(D5:D24=100)*(E5:E24=100),0),0),1)