INDEX MATCH Multiple Criteria in Excel (Without Array Formula)

Non Array Formula with INDEX MATCH in Excel

Two of the widely used functions of Excel are the INDEX function and the MATCH function that can be used to match multiple criteria using both Array Formula and without Array Formula.

Read more: Index Match Multiple Criteria in Rows and Columns in Excel

Today I am going to show how you can sort out some data matching multiple criteria in Excel using Non-Array Formula


Download File



INDEX MATCH Multiple Criteria in Excel (Without Array Formula)


Let us look at this data set. We have the scorecard of students of the annual examination of a school called Saint Joseph School.

We have the Names of the Students in column B, their Marks in Mathematics in column C, their Marks in Physics in column D and their Marks in Chemistry in column E respectively.

A Data set in Excel

Now if anyone asks you the name of the student who achieved 100 in all three subjects, then what will you do? Quite simple. I am showing you how you can sort that out.


Introduction to Excel’s INDEX() and MATCH() Function


Before going to the main point, I just want to give you an idea about Excel’s INDEX() and MATCH() Function. 


1. INDEX() Function

Let us look at the data set again. Assume that someone asked you how much the 11th student obtained in Physics? How will you find that?

If you do not have any idea about the INDEX() Function, you will probably count 1… 2… 3.. in the Student Name column, starting from Mitchel Robbins, up to 11.

Then you will see what the mark of that student in number 11 in Physics is.

In this case, it is Alisha Moor. She achieved 34 in Physics.

But you can find this out quite comfortably using Excel’s INDEX() Function.

INDEX() Function takes three arguments.

  1. A range of cells from which you want to sort out the datum. In our case it is B4:E23. From the name of the first student, Mitchel Robbins to the number obtained in Chemistry by the last student Emily Bronte, 91.
  2. The number of the row of the datum which you want to find out, within the range of cells.. As here we want to know about the 11th student, the row number here is 11.
  3. Also the number of the column of the datum which you want to find out, within the range of cells. As here we want to know about the marks in Physics, the column number here is 3.

So, the syntax for the INDEX() Function is:

=INDEX(range,row_number,column_number)

And in this case, the formula will be:

=INDEX(B4:E23,11,3)

If you select any cell and insert this formula there, you will get the datum of row number 11 and column number 3, from the range of cells B4:E23.

In our case, it is 34 points achieved by the 11th student, Alisha Moor.

INDEX() Function in Excel

Points to Keep in Mind

  • If you have only one row in the range, inserting row number is optional. Excel will automatically take it as 1.
  • Again If you have only one column in the range, inserting 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 raise Reference Error (#REF!)
  • Also If you enter 0 in place of row number, Excel will return the whole column as output. But that will work like an Array Formula then.
  • So you have to select multiple (the required number of) cells and press Ctrl + Shift + Enter.
  • And If you enter 0 in place of column number, Excel will return the whole row as output. But that will work like an Array Formula too.
  • So you have to select multiple (the required number of) cells and press Ctrl + Shift + Enter.

This is the Array Form of the INDEX() function. Besides this, there is another form of the  INDEX() function, called Reference Form. That is not necessary here.

But if you want to know more about these, visit this link.


2. MATCH() Function

The syntax of the MATCH() Function is:

=MATCH(lookup_value, lookup_array, match_type)

The MATCH() Function takes 3 arguments.

  1. A specific text or number.
  2. A range of cells.
  3. Match type (-1, 0, 1). 0 for an exact match.

Then it returns the position of the cell within the range which is matched with the specific text or number.

Going back to our dataset, if we select any cell and enter the formula

=MATCH(“Alisha Moor”,B10:B20,0)

It will return 5. Because Alisha Moor is in the 5th position in the range from B10 to B20.

MATCH() Function in Excel

Points to Keep in Mind:

  • If MATCH() function does not get any match, it returns Value not Available (#N/A!) error.
  • MATCH() function does not distinguish between uppercase and lowercase letters.

If you want to know more about the MATCH() function, visit this link


INDEX and MATCH Multiple Criteria Without Array Formula


Now we go back to our main point. How to sort out the student obtaining 100 in all the three subjects.

There are 3 criteria here. 

  • 100 in Mathematics, that is column B.
  • Also 100 in Physics, that is column C.
  • And100 in Chemistry, that is column D.

Let’s try for one criterion first. Sort out the student with marks 100 only in Mathematics.

First, we get the position of the cell in column C, which contains 100, in the range C4 to C23. We shall use the MATCH() function here.

The formula will be:

=MATCH(100,C4:C23,0)

And you will get 14. Because in the 14th cell of column C, there is a 100.

MATCH Function in Excel

Now use this value to find out the name of the student. It is pretty straightforward. Use the INDEX() function to sort out the 14th value in the Student Name column, in the range B4 to B23.

So the formula will be

=INDEX(B4:B23,14,1)

And you will get Ricky Ben.

INDEX Function in Excel

So the total formula will be

=INDEX(B4:B23,MATCH(100,C4:C23,0),1)

=INDEX(B4:B23,MATCH(100,C4:C23,0),1)

Use this and you will get the same result, Ricky Ben.

INDEX() and MATCH() Together in Excel

Now we sorted out the datum with one criterion. But how can we do this for multiple criteria, without using Array Formula? Here I am showing two ways.


Method 1: Using the CONCATENATE() Function

This is the easier one. First merge the columns to which your criteria belong,  into a new column.

Here the three criteria are 100 in Mathematics, 100 in Physics, and 100 in Chemistry. They belong to columns C, D, and E respectively. So we merge them into a new column F

To merge them, you can either use this formula in the first cell of column F, F4

=CONCATENATE(C4,”,”,D4,”,”,E4)

And then drag the Fill Handle

Or you can directly merge them into F4 by using the Ampersand Symbol (&):

=C4&”,”&D4″,”&E4

And then drag the Fill Handle.

In both cases, you will find the numbers of three subjects of each student merged in one column F like this. 

CONCATENATE() Function in Excel

Then consider this as a single criterion and use the INDEX and MATCH formula for single criterion, shown earlier.

In this case, the formula will be

=INDEX(B4:B23,MATCH(“100,100,100”,F4:F23,0))

And you will get Ricky Ben. Because he is the one with 100 in all three subjects.

INDEX and MATCH Together in Excel

Special Note: Within the MATCH() Function, in the first argument (lookup_value), I covered that between Apostrophes(“”).

Though they were numbers before being merged, after being merged they were converted to texts. That’s why I had to use Apostrophes(“”).


Method 2. Using INDEX() Within INDEX() Function

This is a bit complex. I am showing you step by step so that you can understand.

Let’s compare 100 with each mark in Mathematics. With this formula

=C4:C23=100

It will return an array of Boolean values, TRUE or FALSE. TRUE if any mark is equal to 100, and FALSE otherwise. 

Then do the same for marks in Physics and Chemistry.

=D4:D23=100
=E4:E23=100

Then we multiply the three arrays. 

=(C4:C23=100)*(D4:D23=100)*(E4:E23=100)

Though they were of Boolean values, after multiplication, they will turn into numbers, 1 and 0. 

As anything multiplied by 0 is 0, therefore after multiplication, the resultant array will contain 1 only if there were 1 in all the cells of the row, before multiplication.

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

Then we enter this resultant array within an INDEX() function as range, while keep row number is 0, and column number to 1.

=INDEX((C4:C23=100)*(D4:D23=100)*(E4:E23=100),0,1)

As the row number is 0, the INDEX() function will return the column with number 1, that means the resultant array which we used as the range.

Now we enter this into a MATCH() function and find out if there is any 1 within it. If there is, then where?

=MATCH(1,INDEX((C4:C23=100)*(D4:D23=100)*(E4:E23=100),0,1),0)

Finally, we enter it within another INDEX() function as the row number, with the Name of the Students as its range and the column number as 1.

It will return the Name of the Student in that position.

And we will get the student with 100 in all three subjects. In our case, that is Ricky Ben.

So the complete formula will be

INDEX(B4:B23,MATCH(1,INDEX((C4:C23=100)*(D4:D23=100)*(E4:E23=100),0,1),0),1)

Non Array Formula with INDEX MATCH in Excel

So, we can sort out any datum from any data set using this formula, maintaining multiple criteria.

The best thing is that it is a Non – Array Formula. That means you do not need to press Ctrl + Shift + Enter to enter this formula. Only pressing Enter will do. 


Comparison with Array Formula

There is a comparatively shorter formula to do this. That is

=INDEX(B4:B23,MATCH(1,C4:C23=100*D4:D23=100*E4:E23=100,0),1)

But this is an Array Formula. After writing this into the Formula Bar, you have to press Ctrl + Shift + Enter to enter this.

Excel will automatically put curly braces ({}) around this in the Formula Bar, as a sign of Array Formula.

Array Formula with INDEX MATCH in Excel


Conclusion

Using these methods, you can find out any datum matching multiple criteria from any data set. This is pretty convenient. Do you know any other method? 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.

2 Comments
  1. Reply
    James Matt Ullakko Jul 31, 2021 at 7:50 AM

    Filters on each column you can specify equal to less than or greater than super handy!

Leave a reply

ExcelDemy
Logo