Formula Help

[Rockhopper3]

Hey everybody. Once again I need help with a crazy formula. Here is the formula I "want" to use, but when I press enter or ctrl,shift,enter it says there is an error. Any thoughts?

=IF(B4=0,"",IF(L4="DNS1","DNS",IF(L4="DNS2","DNS",IF(L4="DNF1","DNF",IF(L4="DNF2","DNF",MATCH(TRUE,SMALL(IF($A$4:$A$54=A4,$L$4:$L$54),ROW(INDIRECT("1:"&ROWS($A$4:$A$54))))=L4,0))))))

I use this formula to assign a ranking to a time sheet for a race. There are 2 starts and 2 finshes per race. Someone can start on the first leg, but not on the second. If they don't start the race, I put in DNS1. If they start 1st leg, I put in start time and end time of 1st leg. If they then do not start 2nd leg, I enter DNS2. The same is true if they do not finish, only I use DNF instead of DNS. Please help. Thanks.

 

[Domenic]

Try...

=IF(B4=0,"",IF(OR(L4={"DNS1","DNS2"}),"DNS",IF(OR(L4={"DNF1","DNF2"}),"DNF",MATCH(L4,SMALL(IF($A$4:$A$54=A4,$L$4:$L$54),ROW(INDIRECT("1:"&ROWS($A$4:$A$54)))),0))))

...confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!