index match with multiple criteria

[Neilesh Kumar]

Dear Experts,

Please find here attached workbook, Where i do expect the output in Summary tab based on first criteria at cell H1, the second one is at cell J1 and the last criteria would be at cell A6 as Header criteria from training details tab.

I hope i have clarified my expected output. please revert back if you have any further clarification required.

Thank you for your valuable support and precious phase.

Regards,

Neilesh

[Bo_Ry]

Please try at A7:Exx

=IFERROR(INDEX('Training Details'!A$1:A$30,AGGREGATE(15,6,ROW('Training Details'!$A$7:$A$30)/ISNUMBER(SEARCH(IF($H$1="All","*",$H$1),INDEX('Training Details'!$A$7:$E$30,,MATCH($A$6,'Training Details'!$A$6:$E$6,)))*SEARCH(IF($J$1="All","*",$J$1),'Training Details'!$E$7:$E$30)),ROWS(A$7:A7))),"")

[Neilesh Kumar]

Hi Bo_Ry,

Thank you so much for perfect solution but this one taking couple of seconds to provide expected output. Any idea for the same.

Regards,

Neilesh

[Bo_Ry]

Please try at A7:Exx

=IFERROR(INDEX('Training Details'!A$1:A$30,SMALL(IF(IF($H$1="All",1,INDEX('Training Details'!$A$7:$E$30,,MATCH($A$6,'Training Details'!$A$6:$E$6,))=$H$1)*IF($J$1="All",1,'Training Details'!$E$7:$E$30=$J$1),ROW('Training Details'!$A$7:$A$30)),ROWS(A$7:A7))),"")

Confirm with Ctrl+Shift+Enter


Or Faster with helper column
F1
=COUNTIFS(INDEX('Training Details'!A7:E30,,MATCH(A6,'Training Details'!A6:E6,)),IF(H1="All","*",H1),'Training Details'!E7:E30,IF(J1="All","*",J1))

F7
=IF(ROWS(F$7:F7)>$F$1,"",SMALL(IF(IF($H$1="All",1,INDEX('Training Details'!$A$7:$E$30,,MATCH($A$6,'Training Details'!$A$6:$E$6,))=$H$1)*IF($J$1="All",1,'Training Details'!$E$7:$E$30=$J$1),ROW('Training Details'!$A$7:$A$30)),ROWS(F$7:F7)))

A7:Exx
=IF($F7="","",INDEX('Training Details'!A$1:A$30,$F7))


or VBA

Please Login or Register  to view this content.

[Neilesh Kumar]

Hi Bo_Ry,

Thank you so much for valuable support. The upgraded formula works much faster than the previous.

Thank you once again for precious phase.

Regards,

Neilesh