# index match with multiple criteria

1. ## index match with multiple criteria

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

2. ## Re: index match with multiple criteria

=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))),"")

3. ## Re: index match with multiple criteria

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

4. ## Re: index match with multiple criteria

=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.``

5. ## Re: index match with multiple criteria

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

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1