In this article, we will show you two quick methods for how to use the ** INDEX **and

**functions for multiple criteria without an array in Excel. Two of the most widely used functions of Excel are the**

*MATCH***function and the**

*INDEX***function, which can be used to match multiple criteria using both array formula and without array formula. Today we are going to show you how you can sort out some data matching multiple criteria in Excel using a non-array formula.**

*MATCH*## 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 a 34 in physics. But you can find this out quite comfortably using Excelâ€™s

**Function.**

*INDEX*The** INDEX** function takes three arguments.

- A range of cells from which you want to sort out the datum. In our case it is
**B5:E24**. 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*

`=INDEX(range,row_number,column_number)`

- And in this case, the formula will be:

`=INDEX(B5:F24,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 ** B5:E24. **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.**Again 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 raise 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 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 the 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

**function, called the reference form. That is not necessary here.**

*INDEX*## MATCH Function

Now, let us discuss ** the MATCH function** of Excel. The syntax of the

**function is:**

*MATCH*`=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 that 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",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 get any match, it returns a**Value not Available (#N/A!)**error.*The***MATCH**function does not distinguish between uppercase and lowercase letters.

## Use INDEX MATCH for Multiple Criteria Without Array: 2 Handy Approaches

We will show you two quick methods to achieve our goal. Now, let’s go back to our main point. How to sort out the student obtaining a 100 in all three subjects? There are 3 criteria here.

- 100 in Mathematics, which is column
.*B* - Also, 100 in Physics, is column
.*C* - And 100 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

**to**

*C5***. We shall use the**

*C24***function here. The formula will be:**

*MATCH*`=MATCH(100,C5:C24,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

**to**

*B5***.**

*B24*So the formula will be

`=INDEX(B5:B24,14,1)`

And you will get Ricky Ben.

So the total formula will be

`=INDEX(B5:B24,MATCH(100,C5:C24,0),1)`

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

Now we sorted out the data with one criterion. But how can we do this for multiple criteria without using an array formula? Here we are showing two ways.

### Method 1: Using Helper Column

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

**and**

*D*,**respectively. So, we combine them into a new column,**

*E,***, using**

*F***.**

*the CONCATENATE function***Steps:**

- To merge them, you can either use this formula in the first cell of column
.*F*,*F5*

`=CONCATENATE(C5,",",D5,",",E5)`

- Then, drag the
to*Fill Handle*the formula into the rest of the cells.*AutoFill* - Or you can directly merge them into
*F5*.*Fill Handle*

`=C5&","&D5&","&E5`

- In both cases, you will find the numbers of three subjects of each student merged into one column
*F*

- Then consider this as a single criterion and use the
and*INDEX*formula for a single criterion, shown earlier. In this case, the formula will be –*MATCH*

`=INDEX(B5:B24,MATCH("100,100,100",F5:F24,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**), we covered that between **apostrophes (â€œ”). **Though they were numbers before being merged, after being merged they were converted to text. Thatâ€™s why we had to use **apostrophes (“”)**.

### Method 2: Applying Nested INDEX and MATCH Functions

In this section, we’ll demonstrate how to use nested** INDEX** and

*functions to match multiple criteria at once. This is a bit complex to understand. However, we are showing you step by step so that you can understand clearly.*

**MATCH****Steps:**

- To begin with, type the following formula in cell
and press*G5*.*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 are comparing 100 with each mark in Mathematics with this part of the formula. It will return an array of
**Boolean**values,**True**or**False**.**True**if any mark is equal to 100, and**False**otherwise.

- Firstly, we are comparing 100 with each mark in Mathematics with this part of the formula. It will return an array of
- Secondly, we are doing the same for marks in Physics and Chemistry.
**(C5:C24=100)*(D5:D24=100)*(E5:E24=100)**- Thirdly, we multiply the three values from the previous steps. Despite having Boolean values, they 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)**- Then, we enter this resultant array within an
function as range, while keep row number is 0, and column number to 1.*INDEX* - As the row number is 0, the
function will return the column with the number 1, which means the resultant array which we used as the range.*INDEX*

- Then, we enter this resultant array within an
**MATCH(1,INDEX((C5:C24=100)*(D5:D24=100)*(E5:E24=100),0,1),0)**- Afterward, we enter this into a
function and find out if there is any 1 within it.*MATCH*

- Afterward, we enter this into a
**INDEX(B5:B24,MATCH(1,INDEX((C5:C24=100)*(D5:D24=100)*(E5:E24=100),0,1),0),1)**- Finally, we enter it within another
function as the row number, with the name of the students as its range and the column number as 1.*INDEX* - 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.

- Finally, we enter it within another

- So, we can sort out any data 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
to enter this formula. Only pressing*Ctrl+Shift+Enter*will do.*Enter*

## Comparison with Array Formula

There is a comparatively shorter formula for using * INDEX MATCH* for multiple criteria without an array. But this is an array formula. After writing this into the formula bar, you have to press

**to enter this. Excel will automatically put**

*Ctrl+Shift+Enter***curly braces ({})**around this in the formula bar, as a sign of an array formula

**.**However, we are using the Microsoft 365 version, so we can simply press

*and it will not show a curly bracket for this version. The the formula is –*

**Enter**`=INDEX(B5:B24,MATCH(1,(C5:C24=100)*(D5:D24=100)*(E5:E24=100),0),1)`

## Using INDEX MATCH with Multiple Criteria for Date Range

We want to extract the price for a certain product on a specific date. 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. Let’s follow the instructions below to use the **INDEX MATCH** formula with multiple criteria for date range!

**Steps:**

- Firstly, type 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))`

- Then, press
if you are using Microsoft 365. However, if you are using older versions of Excel, then press*Enter*.*Ctrl+Shift+Enter*

**Formula Breakdown**

- Firstly, in the formula,
**$E$5:$E$16**refers to the array argument. Inside thefunction*MATCH***$B$5:$B$16=B19**,**$D$5:$D$16>=C19**, and**$C$5:$C$16<=C19**declare the criteria. - Secondly, the
function locates the position of a given value within a row, column, or table. As we said earlier, the*MATCH*portion passes the row number for the*MATCH*function.*INDEX* - Thirdly, the
portion assigns 1 as lookup_value,*MATCH***($B$5:$B$16=G5)*($D$5:$D$16>=H5)*($C$5:$C$16<=H5)**as lookup_array, and 0 declares the [match_type] as an exact match. - Finally, the used
formula returns 3 as it finds Ice Cream in row number 3.*MATCH*

**Download Practice Workbook**

You can download the Excel file from the link below.

## Conclusion

We have shown you two quick methods of how to use the ** INDEX **and

**functions for multiple criteria without an array in Excel. So, using these methods, you can find any data matching multiple criteria from any data set. This is pretty convenient. Do you know any other methods? Let us know in the comment section.**

*MATCH***<< Go Back to Multiple Criteria | INDEX MATCH | Formula List | Learn Excel**

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)