Index Match multiple criteria non array

tiredofit

Well-known Member
Joined
Apr 11, 2013
Messages
1,818
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,212,933
Messages
6,110,751
Members
448,295
Latest member
Uzair Tahir Khan

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
Back
Top