Index Match multiple criteria non array

tiredofit

Well-known Member
Joined
Apr 11, 2013
Messages
1,414
According to this article:

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
Joined
Oct 20, 2009
Messages
8,388
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Apr 11, 2013
Messages
1,414
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
Joined
Dec 30, 2008
Messages
12,640
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top