Results 1 to 7 of 7

Thread: Help with index match iferror formula

  1. #1
    Acolyte TTTT's Avatar
    Join Date
    Jul 2018
    Posts
    23
    Articles
    0
    Excel Version
    365

    Red face Help with index match iferror formula



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi amazing excel formula minds,

    I've been trying to extend my current formula to span about 11 different rosters (in another tab titled Roster!), but I havent had much luck extended the below:
    =IFERROR(IF($C$7=Roster!$C$2,INDEX(Roster!$C:$C,MATCH(F$10,Roster!$B:$B,0)),IF($C$7=Roster!$D$2,INDEX(Roster!$D:$D,MATCH(F$10,Roster!$B:$B,0)),IF($C$7=Roster!$E$2,INDEX(Roster!$E:$E,MATCH(F$10,Roster!$B:$B,0)),""))),"")

    Basically just need the top replicated 11 times (each index match is for 1 roster type names go across ie. $C$2, $D$2, $E$2 etc through to $M$2) and roster patterns associated to each roster name go down the list ie. $C:$C, $D:$D, $E:$E etc through to $M:$M, the match codes will remain the same throughout the formula - hope this makes sense.

    I usually just copy and paste entire IF(Index, Match) formula to replicate, but I cant quite figure out the end part where the closing parenthesis goes

    Any help would be amazing!

    Thank you in advance

  2. #2
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,290
    Articles
    0
    Excel Version
    Office 365 Subscription
    You need to match the number of opening brackets with closing ones:

    =IF($C$7=Roster!$C$2,INDEX(Roster!$C:$C,MATCH(F$10,Roster!$B:$B,0)),IF($C$7=Roster!$D$2,INDEX(Roster!$D:$D,MATCH(F$10,Roster!$B:$B,0)),IF($C$7=Roster!$E$2,INDEX(Roster!$E:$E,MATCH(F$10,Roster!$B:$B,0))

    You appear to have two missing, so try this:

    =IF($C$7=Roster!$C$2,INDEX(Roster!$C:$C,MATCH(F$10,Roster!$B:$B,0)),IF($C$7=Roster!$D$2,INDEX(Roster!$D:$D,MATCH(F$10,Roster!$B:$B,0)),IF($C$7=Roster!$E$2,INDEX(Roster!$E:$E,MATCH(F$10,Roster!$B:$B,0))))

    Then add the IFERROR:

    =IFERROR(IF($C$7=Roster!$C$2,INDEX(Roster!$C:$C,MATCH(F$10,Roster!$B:$B,0)),IF($C$7=Roster!$D$2,INDEX(Roster!$D:$D,MATCH(F$10,Roster!$B:$B,0)),IF($C$7=Roster!$E$2,INDEX(Roster!$E:$E,MATCH(F$10,Roster!$B:$B,0)))),"")


    Ali
    Enthusiastic self-taught user of MS Excel!

  3. #3
    Acolyte TTTT's Avatar
    Join Date
    Jul 2018
    Posts
    23
    Articles
    0
    Excel Version
    365
    Hi AliGW
    Thank you so much, but what about if I want to add more IF Index Match scenarios into it? example below following the sequence above
    =iferror(IF($C$7=Roster!$C$2,INDEX(Roster!$C:$C,MATCH(F$10,Roster!$B:$B,0)),IF($C$7=Roster!$D$2,INDEX(Roster!$D:$D,MATCH(F$10,Roster!$B:$B,0)),IF($C$7=Roster!$E$2,INDEX(Roster!$E:$E,MATCH(F$10,Roster!$B:$B,0)),IF($C$7=Roster!$f$2,INDEX(Roster!$f:$f,MATCH(F$10,Roster!$B:$B,0)),IF($C$7=Roster!$g$2,INDEX(Roster!$g:$g,MATCH(F$10,Roster!$B:$B,0)),IF($C$7=Roster!$h$2,INDEX(Roster!$h:$h,MATCH(F$10,Roster!$B:$B,0)),IF($C$7=Roster!$i$2,INDEX(Roster!$i:$i,MATCH(F$10,Roster!$B:$B,0)),IF($C$7=Roster!$j$2,INDEX(Roster!$j:$j,MATCH(F$10,Roster!$B:$B,0)),IF($C$7=Roster!$k$2,INDEX(Roster!$k:$k,MATCH(F$10,Roster!$B:$B,0)),IF($C$7=Roster!$l$2,INDEX(Roster!$l:$l,MATCH(F$10,Roster!$B:$B,0)),IF($C$7=Roster!$m$2,INDEX(Roster!$m:$m,MATCH(F$10,Roster!$B:$B,0)))),"")

  4. #4
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,290
    Articles
    0
    Excel Version
    Office 365 Subscription
    It’s the same - just make sure that you have the same number of IF( as you do ) at the end. Excel conveniently colour codes them for you.

    I think you have 10 here, so I’ve balanced that at the end. Hopefully it works, but if not, do the balancing yourself.

    =iferror(IF($C$7=Roster!$C$2,INDEX(Roster!$C:$C,MATCH(F$10,Roster!$B:$B,0)),IF($C$7=Roster!$D$2,INDEX(Roster!$D:$D,MATCH(F$10,Roster!$B:$B,0)),IF($C$7=Roster!$E$2,INDEX(Roster!$E:$E,MATCH(F$10,Roster!$B:$B,0)),IF($C$7=Roster!$f$2,INDEX(Roster!$f:$f,MATCH(F$10,Roster!$B:$B,0)),IF($C$7=Roster!$g$2,INDEX(Roster!$g:$g,MATCH(F$10,Roster!$B:$B,0)),IF($C$7=Roster!$h$2,INDEX(Roster!$h:$h,MATCH(F$10,Roster!$B:$B,0)),IF($C$7=Roster!$i$2,INDEX(Roster!$i:$i,MATCH(F$10,Roster!$B:$B,0)),IF($C$7=Roster!$j$2,INDEX(Roster!$j:$j,MATCH(F$10,Roster!$B:$B,0)),IF($C$7=Roster!$k$2,INDEX(Roster!$k:$k,MATCH(F$10,Roster!$B:$B,0)),IF($C$7=Roster!$l$2,INDEX(Roster!$l:$l,MATCH(F$10,Roster!$B:$B,0)),IF($C$7=Roster!$m$2,INDEX(Roster!$m:$m,MATCH(F$10,Roster!$B:$B,0)))))))))))),"")
    Ali
    Enthusiastic self-taught user of MS Excel!

  5. #5
    Acolyte TTTT's Avatar
    Join Date
    Jul 2018
    Posts
    23
    Articles
    0
    Excel Version
    365
    Hi AliGW,
    Thanks so much, I tried that before I emailed it out but it didnt work, I copied and pasted your version and it didnt work either, I tried to add 1 more ) and it didnt even work, then I tried it again and it seems to have accepted it! YEY

  6. #6
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,290
    Articles
    0
    Excel Version
    Office 365 Subscription
    It's just a case of counting carefully. YOu have to remember in your case that the final bracket of the formula is not one of the closing IF brackets, so you need to discount thatone:

    ... INDEX(Roster!$E:$E,MATCH(F$10,Roster!$B:$B,0) - this belongs to the INDEX statement
    Ali
    Enthusiastic self-taught user of MS Excel!

  7. #7
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,682
    Articles
    0
    Excel Version
    O365
    There are a number of techniques you can bring to debugging long formulas. One I use a lot is to split the statements over multiple lines, So the formula that you were given, if written like so

    =IFERROR(IF($C$7=Roster!$C$2,INDEX(Roster!$C:$C,MATCH(F$10,Roster!$B:$B,0)),
    IF($C$7=Roster!$D$2,INDEX(Roster!$D:$D,MATCH(F$10,Roster!$B:$B,0)),
    IF($C$7=Roster!$E$2,INDEX(Roster!$E:$E,MATCH(F$10,Roster!$B:$B,0)),
    IF($C$7=Roster!$f$2,INDEX(Roster!$f:$f,MATCH(F$10,Roster!$B:$B,0)),
    IF($C$7=Roster!$g$2,INDEX(Roster!$g:$g,MATCH(F$10,Roster!$B:$B,0)),
    IF($C$7=Roster!$h$2,INDEX(Roster!$h:$h,MATCH(F$10,Roster!$B:$B,0)),
    IF($C$7=Roster!$i$2,INDEX(Roster!$i:$i,MATCH(F$10,Roster!$B:$B,0)),
    IF($C$7=Roster!$j$2,INDEX(Roster!$j:$j,MATCH(F$10,Roster!$B:$B,0)),
    IF($C$7=Roster!$k$2,INDEX(Roster!$k:$k,MATCH(F$10,Roster!$B:$B,0)),
    IF($C$7=Roster!$l$2,INDEX(Roster!$l:$l,MATCH(F$10,Roster!$B:$B,0)),
    IF($C$7=Roster!$m$2,INDEX(Roster!$m:$m,MATCH(F$10,Roster!$B:$B,0)))))))))))),"")

    becomes a lot clearer to se what is going on, and check to be syntactically correct.

    Another trick is to copy the code to a tab in Notepad++ and then when you select a bracket, it will show which is the corresponding bracket, so you can check for correctness this way.

Posting Permissions

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