Help with index match iferror formula

TTTT

New member
Joined
Jul 18, 2018
Messages
25
Reaction score
0
Points
0
Excel Version(s)
365
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
 
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)))),"")


 
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)))),"")
 
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)))))))))))),"")
 
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
 
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
 
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.
 
Back
Top