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

**Table of Contents**hide

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

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.

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

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

- A specific text or number.
- A range of cells.
- 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**.

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

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.

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.

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.

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.

**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)**

** **

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

**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:

- INDEX MATCH with 3 Criteria in Excel (4 Examples)
- Excel Index Match single/multiple criteria with single/multiple results
- INDEX, MATCH and MAX with Multiple Criteria in Excel
- How to Match Multiple Criteria from Different Arrays in Excel
- Index Match with Multiple Matches
- How to use INDEX & MATCH worksheet functions in Excel VBA

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

Thanks, James!