# Index Match multiple criteria non array

#### tiredofit

##### Well-known Member

Code:
``https://exceljet.net/formula/index-and-match-with-multiple-criteria``

it is possible to enter a non-array formula to replicate an Index Match with multiple criteria.

The formula is:

Code:
``=INDEX(rng1,MATCH(1,INDEX((A1=rng2)*(B1=rng3)*(C1=rng4),0,1),0))``

and it states:

Code:
``The INDEX function can handle arrays natively, so the second INDEX is added only to "catch" the array created with the boolean logic operation and return the same array again to MATCH. To do this, INDEX is configured with zero rows and one column. The zero row trick causes INDEX to return column 1 from the array (which is already one column anyway).``

When I apply it to the data:

Code:
``=INDEX(E5:E11,MATCH(1,INDEX((H5=B5:B11)*(H6=C5:C11)*(H7=D5:D11),0,1),0))``

I do get the correct answer.

What I don't understand is when I evaluated this part of the formula:

Code:
``INDEX((H5=B5:B11)*(H6=C5:C11)*(H7=D5:D11),0,1)``

I got a value of 0, so I tried this:

Code:
``=INDEX(E5:E11,MATCH(1,0,0))``

but it returned a value of #N/A.

Why couldn't I replace the Index part with a 0 to get the correct result?

Thanks

### Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

#### steve the fish

##### Well-known Member
You have just misunderstood. You need to look at it as a whole formula eg

=INDEX(A5:A11,MATCH(1,INDEX((H5=B5:B11)*(H6=C5:C11)*(H7=D5:D11),0),0))

The 2nd index will produce an array of 1s and 0s where the position of the one is returned to the first index. To see whats going on evaluate the formula using the evaluate formula tool on the formulas menu. Its still an array formula as such just doesnt need the CSE.

#### tiredofit

##### Well-known Member
You have just misunderstood. You need to look at it as a whole formula eg

=INDEX(A5:A11,MATCH(1,INDEX((H5=B5:B11)*(H6=C5:C11)*(H7=D5:D11),0),0))

The 2nd index will produce an array of 1s and 0s where the position of the one is returned to the first index. To see whats going on evaluate the formula using the evaluate formula tool on the formulas menu. Its still an array formula as such just doesnt need the CSE.
Thanks for clarifying.

#### jasonb75

##### Well-known Member
Technically it's not a non-array formula, it still processes as an array, the second INDEX function is used to circumvent the CSE confirmation.

Since the introduction of AGGREGATE in excel 2010, the method in that link (at least for the purpose shown) is becoming less common in favour of something like this.
Excel Formula:
``=INDEX(rng1,AGGREGATE(15,6,ROW(rng2)/(A1=rng2)/(B1=rng3)/(C1=rng4),1))``
There are various methods. Personally, I use the entire column in rng1, while many others use the actual range and adjust the row in the array (more complex, less efficient)
Excel Formula:
``=INDEX(rng1,AGGREGATE(15,6,(ROW(rng2)-MIN(ROW(rng1))+1)/(A1=rng2)/(B1=rng3)/(C1=rng4),1))``
With numeric data, it can be made simpler as long as it should be a unique match, otherwise it would return the lowest value rather than the first match.
Excel Formula:
``=AGGREGATE(15,6,rng1/(A1=rng2)/(B1=rng3)/(C1=rng4),1)``
The added benefit of this method it that it allows multiple results to be returned if there is more than one matching row when a sequential count is used at the end of the formula (which can not be done with MATCH)
Excel Formula:
``=INDEX(rng1,AGGREGATE(15,6,ROW(rng2)/(A1=rng2)/(B1=rng3)/(C1=rng4),ROWS(\$A\$1:\$A1)))``
And those are just a few examples of a more versatile method that come to mind.

Replies
4
Views
418
Replies
4
Views
363
Replies
3
Views
111
Replies
8
Views
205
Replies
6
Views
81

### Forum statistics

1,129,814
Messages
5,638,493
Members
417,029
Latest member
lingx86 ### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?    1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option. Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com". Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back