Results 1 to 3 of 3

Thread: INDEX MATCH formula HELP
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jul 2019
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default INDEX MATCH formula HELP

    A B C D E F G H I
    1
    2 2662 50% a d
    3 1331 20% b f
    4
    5 a b c d e f g h
    6 2662 50% 0% 0% 50% 0% 0% 0% 0%
    7 1331 0% 20% 0% 0% 0% 20% 0% 0%
    8
    9

    Hi everyone

    May I know what array formula should I type in Cell D2 in order to retrieve the result..

    pls help, tried many INDEX MATCH formula but still failed

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,353
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

    Default Re: INDEX MATCH formula HELP

    Hi & welcome to MrExcel.
    In D2 copied down & across
    =IFERROR(INDEX($B$5:$I$5,AGGREGATE(15,6,(COLUMN($B$5:$I$5)-COLUMN($B$5)+1)/(($A$6:$A$7=$A2)*($B$6:$I$7=$B2)),COLUMNS($A$1:A$1))),"")
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  3. #3
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,330
    Post Thanks / Like
    Mentioned
    72 Post(s)
    Tagged
    14 Thread(s)

    Default Re: INDEX MATCH formula HELP

    Welcome to the forum

    Another way with array forumula

     ABCDEFGHI
    1         
    2266250% ad    
    3133120% bf    
    4         
    5 abcdefgh
    6266250%0%0%50%0%0%0%0%
    713310%20%0%0%0%20%0%0%

    CellArray Formula
    D2{=IFERROR(INDEX($A$5:$I$5,0,SMALL(IF($A$6:$A$7=$A2,IF($B$6:$I$7=$B2,COLUMN($B$5:$I$5))),COLUMNS($D$1:D1))),"")}


    Array formulas
    Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
    Note: Do not try and enter the {} manually yourself
    Regards Dante Amor

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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