Index Match multiple criteria help

[Swifty87]

Hey All,

I have been scratching my head trying to add in new logic to my formula below:

{=IFERROR(INDEX($C$29:$J$61, SMALL(IF(($C$3<=$D$29:$D$61)*($C$4>=$E$29:$E$61)*($C$8<=$F$29:$F$61)*($C$10<=$G$29:$G$61)*($C$11<=$H$29:$H$61)*($C$12<=$J$29:$J$61)*($C$9=$K$29:$K$61)*("yes"=$M$29:$M$61)*("No"=$L$29:$L$61), MATCH(ROW($C$29:$C$40), ROW($C$29:$C$40))), ROWS($A$3:A3)), COLUMN(A3)),"Pre-set criteria not met, Manually select M/ch Ctr’s")}

i want to add in a new piece that if cell C8 is < 12.0 and C6 is =< what is in N29:N43 then return what is in that match in C29:C43, where i am struggling as it goes against what i have built in above, i need it to us the criteria i have just stated if cell C8 is <12.0

Formula is in H4

I will keep plugging away but so far i am coming up a blank.

Any and all help very much appreciated

Thanks
GK

 

[Swifty87]

Managed to get it to work by adding an extra 'if' using the following:

=IFERROR(IF(C8>12,INDEX(C:C,AGGREGATE(15,6,ROW(C29:C61)/(C3<=D29:D61)/(C4>=E29:E61)/(C9=K29:K61)/(C10<=G29:G61)/(C11<=H29:H61)/(C12<=J29:J61)/(L29:L61="no")/(M29:M61="yes")/(C6<=N29:N61),1)),INDEX(C:C,AGGREGATE(15,6,ROW(C29:C61)/(C3<=N29:N61)/(C4>=E29:E61)/(C8<=F29:F61)/(C9=K29:K61)/(C10<=G29:G61)/(C11<=H29:H61)/(C12<=J29:J61)/(L29:L61="no")/(M29:M61="yes")/(C6<=N29:N61),1))),"Pre-set criteria not met, Manually select M/ch Ctr’s")