Index Match Multiple Criteria in Rows and Columns in Excel

INDEX-MATCH AND Type Multiple Criteria Array Formula

Today I will be showing how to sort out cells matching multiple criteria in rows and columns in Microsoft Excel using the INDEX and the MATCH functions.


Download Practice Workbook


Index Match Multiple Criteria in Rows and Columns in Excel

Let us have a data set like this. We have the Annual Examination Record of a school named The Rose Valley Kindergarten.

We have the Student Names in column B, and their marks in History, Sociology and Literature in columns C, D, and E respectively.

A data set in Excel


Case 1:  Matching for Single Criteria

First of all, let us try to search for the students with a single criteria

We shall use Excel’s INDEX() and MATCH() functions for this purpose.

If you do not have a preliminary idea about Excel’s INDEX() and MATCH() function, visit this link.


Type 1: Single Criteria Equal to a Value

Let us try to sort out the students who got 100 in History.

The formula here will be

=INDEX(B4:B19,MATCH(100,C4:C19,0))

INDEX-MATCH functions in Excel

  • Here MATCH(100,C4:C19,0) returns the row number of the first cell in the array C4 to C19 which contains 100. In this case, it is C12.
  • With this row number, INDEX(B4:B19,MATCH(100,C4:C19,0)) returns the content of the cell in the array B4 to B19 taking column number equal to 1. In this case, it is Mark Taylor.

So, Mark Taylor is the first student with a 100 in History.

But we see, there is another student with a 100 in History. Cindy Shepherd.

In fact, there can be many more.

And we want all of them together, not only the first one. Isn’t it?

That is the limitation of using INDEX-MATCH. You will only get the first one.

But no worry, we will get all of them together using Excel’s FILTER() function soon.

Before it, let’s dive into a bit more complex thing.


Type 2: Single Criteria Greater than or Less than a value

Not if one asks to know the names of the students who got not exactly 100, but more than 95 in History, then?

What will you do?

No worries. There are formulas. They are a bit complex, but precise.


Method 1: Using Array Formula

The Array Formula to know the Names of the students who got more than 95 in History is

=INDEX(B4:B19,MATCH(TRUE,(C4:C19)>95,0))

INDEX MATCH Array Formula

See, we have got Shane Austin, the first student with more than 95 in History.

Note: As this is an Array Formula, you have to press Ctrl + Shift + Enter to enter the formula (Not necessary in Office 365)


Method 2: Using Non-Array Formula

If you do not want to use the Array Formula, use this Non – Array Formula

=INDEX(B4:B19,MATCH(TRUE,INDEX((C4:C19)>95,0,1),0))

INDEX MATCH Non-Array Formula

See, we have got Shane Austin, the first student with more than 95 in History.

Whether using Array or Non-Array Formula, we get the same old problem.

We want all the names simultaneously, don’t we?

Okay. Now we will do that using Excel’s FILTER() function.


Type 3: Using FILTER() Function

Before diving into more details, a short reminder. FILTER() function is only available in Office 365.

Syntax of FILTER() Function

=FILTER(array, include, [if_empty])
  • Takes three arguments. One range of cells called an array, one specific criterion called include, and one value called if_empty that will be returned in case any cell of the array does not maintain the criteria called include.
  • Returns all the cells of the array which maintain the criteria include.
  • The criteria include can be of the same column as an array, or of any other column.
  • The argument if_empty is optional. The default value is No Result.

So, if we want to find the name of the students with more than 95 in History, the formula will be

=FILTER(B4:B19,C4:C19>95)

FILTER() function in Excel

Now see, we have got all the four students who got more than 95 in History.

Note: if you want to sort out all the cells with a criterion equal to a given value, the process is the same.

For example, to sort out all the students with 100 in History, the formula will be

=FILTER(B4:B19,C4:C19=100)

Case 2: Matching for Multiple Criteria


Type 1: Multiple Criteria of OR Type

Now let us try another different thing. Try to sort out all the students who got more than 95 in any of the three subjects, History, Sociology and Literature.

We can try it using both INDEX()-MATCH() and FILTER() functions.

But like earlier, INDEX()-MATCH() will only return the name of the first student who fulfills this criterion.

Let’s try it first.


Method 1: Getting only the First Match Using INDEX() and MATCH() Function


Array Formula

First, we will use an Array Formula.

Use this formula to find out the students who got more than 95 in any subject

=INDEX(B4:B19,MATCH(TRUE,(((C4:C19)>95)+((D4:D19)>95)+((E4:E19)>95))>0,0))

INDEX-MATCH OR Type Multiple Citeria Array Formula

See, we have got only the first student, Shane Austin.

Note: As this is an Array Formula. You have to press Ctrl + Shift + Enter to enter the formula (Not in Office 365)


Non-Array Formula

If you do not want to use the Array Formula, use this Non-Array Formula

=INDEX(B4:B19,MATCH(TRUE,INDEX((((C4:C19)>95)+((D4:D19)>95)+((E4:E19)>95))>0,0,1),0))

INDEX-MATCH OR Type Multiple Citeria Non-Array Formula

See, we have again got the first student, Shane Austin.


Method 2: Getting All the Matches Using FILTER() Function

You can sort out all the students who got more than 95 in any of the three subjects using Excel’s FILTER() function.

To know more about the FILTER() function, go to section 1.3

The formula that we will use here is

=FILTER(B4:B19,(((C4:C19)>95)+((D4:D19)>95)+((E4:E19)>95))>0)

OR Type Multiple Criteria Filter Function

This time, see that we have filtered out all the five students who got more than 95 in any of the three subjects.


Type 2: Multiple Criteria of AND Type

Finally, we want to extract the names of the students who got more than 90 in all three subjects.

We can extract it using Excel’s INDEX()-MATCH() and FILTER() functions again.


Method 1: Getting only the First Match Using INDEX() and MATCH() Function


Array Formula

First of all, we will accomplish this using an Array Formula.

To extract the names of the students who got more than 90 in all three subjects, the formula will be

=INDEX(B4:B19,MATCH(1,(((C4:C19)>90)*((D4:D19)>90)*((E4:E19)>90)),0))

INDEX-MATCH AND Type Multiple Criteria Array Formula

See, we have got Shane Austin, the first student who got more than 90 in all three subjects.

Note: As this is an Array Formula, you have to press Ctrl + Shift + Enter to enter the formula (Not necessary in Office 365)


Non-Array Formula

If you don’t want to use an Array Formula, you can use this Non-Array Formula

=INDEX(B4:B19,MATCH(1,INDEX((((C4:C19)>90)*((D4:D19)>90)*((E4:E19)>90)),0,1),0))

INDEX-MATCH AND Type Multiple Criteria Non-Array Formula

See, we have again got Shane Austin, the first student who got more than 90 in all three subjects.


Method 2: Getting All the Matches Using FILTER() Function

The FILTER() function will return us the names of all the students who got more than 90 in all three subjects.

The  formula will be:

=FILTER(B4:B19,(((C4:C19)>90)*((D4:D19)>90)*((E4:E19)>90))=1)

AND Type Multiple Criteria Filter Function in Excel

See, we have filtered out all the students who got more than 90 in all three subjects.


Conclusion

Using these methods, we can sort out any data satisfying single or multiple criteria in Excel. Do you know any other method? Or do you have any questions? Feel free to let us know in the comment section.


Further Readings

Rifat Hassan

Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo