Array formula - index and match with multiple statements

Give a try to ths ARRAY formula.

=INDEX(E$3:E$11,SMALL(IF(($A$3:$A$11=G3)*($B$3:$B$11=H3)*($C$3:$C$11=I3)*($D$3:$D$11>=J3)*($D$3:$D$11<=K3),ROW(E$3:E$11)-2),ROW($E$1)))

That works, many thanks.

Could you possibly explain it to me please? I am trying to use this spreadsheet as a lead up to the real one I need to do which compares multiple conditions for 11,300 rows to 9000 rows.

I was hoping I could fix my formula above in an effort to understand how it works.


edit: Pete_UK, yes, the median term works well for finding if a date is between 2 dates, so i was trying to use that

=INDEX(E$3:E$11,SMALL(IF(($A$3:$A$11=G3)*($B$3:$B$11=H3)*($C$3:$C$11=I3)*($D$3:$D$11>=J3)*($D$3:$D$11<=K3),ROW(E$3:E$11)-2),ROW($E$1)))

The INDEX part of the formula you know how it works,

The small(if( part of the formula describe the conditions..

The ROW part ..ROW(E$3:E$11)-2), actually "says" in which row the formula must start searcing. As your Data start in row 3, formula is .ROW(E$3:E$11)-2). If your data start in row 25..formula should be ...ROW(E$3:E$11)-24).

ROW($E$1))) This locked by $ symbol, means that we need just one result for eatch case..

Hope this helps you.

Thanks for that. What does the * mean in between the statements?

edit: oh its just multiplied? so 1*1*1*1*1

In fact(in this formula), * sign, replaces the AND function...

So if( $A$3:$A$11=G3)AND($B$3:$B$11=H3)AND($C$3:$C$11=I3)AND........

As an alternative, here's a slightly different approach, doesn't require Ctrl+Shift+Enter:
=INDEX($E$3:$E$11,MATCH(1,INDEX(($A$3:$A$11&$B$3:$B$11&$C$3:$C$11=G3&H3&I3)*($D$3:$D$11>=J3)*($D$3:$D$11<=K3),),0))

I have a rule that says:

"If you have a not ARRAY formula(a regular formula) that works for you, then for sure is the best way to go on. Forget the Array formula"

Mine is basically an array formula (it calculates like one), it just doesn't need to be entered with ctrl+shift+enter. I basically used an extra INDEX() function to do the array portion automatically so that you don't have to tell Excel it's an array formula.

Also instead of (range1=condition1)*(range2=condition2)*... I used this format: (range1&range2=condition1&condition2)
Think of those two formats as being interchangeable (the same thing written differently).

Other than those minor changes, its basically the same formula proposed earlier by Fotis and Pete

Very nice gesture.

We thank you for that.

Just to note, on a large dataset (12,000 compared to 9,000), multiple vlookups with helper columns is a lot quicker to run than index and match.

CPU has been maxed out at 100% on all 8 cores, memory usage at 1gb, for about 20 minutes so far! The vlookups calculate in about 2 minutes. So Pete_UK, you were totally right! (although I am using the non array formula)


edit: 25 minutes so far, and it's on 20% calculating