+ Reply to Thread
Results 1 to 5 of 5

index match with multiple criteria

  1. #1
    Forum Contributor Neilesh Kumar's Avatar
    Join Date
    05-26-2016
    Location
    INDIA
    MS-Off Ver
    2013 & 2016
    Posts
    739

    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
    Attached Files Attached Files

  2. #2
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    6,021

    Re: index match with multiple criteria

    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))),"")
    Attached Files Attached Files

  3. #3
    Forum Contributor Neilesh Kumar's Avatar
    Join Date
    05-26-2016
    Location
    INDIA
    MS-Off Ver
    2013 & 2016
    Posts
    739

    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. #4
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    6,021

    Re: index match with multiple criteria

    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.
    Attached Files Attached Files

  5. #5
    Forum Contributor Neilesh Kumar's Avatar
    Join Date
    05-26-2016
    Location
    INDIA
    MS-Off Ver
    2013 & 2016
    Posts
    739

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. VBA function to match multiple criteria faster than vlookup or index match
    By bkav1991 in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 11-09-2020, 09:14 AM
  2. Replies: 1
    Last Post: 03-25-2020, 08:06 AM
  3. Replies: 1
    Last Post: 03-01-2020, 10:36 PM
  4. INDEX MATCH from table with multiple MATCH criteria
    By nostrum in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-30-2019, 11:41 AM
  5. Replies: 16
    Last Post: 01-05-2018, 11:04 PM
  6. Need help in Index, Match usage to match multiple criteria in sum function
    By Summer0830 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-26-2017, 02:47 AM
  7. Replies: 2
    Last Post: 09-27-2014, 04:34 PM

Bookmarks

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