creating a tournament fixture

[suparno]

I want to prepare an automated fixture for our cricket tournament. We have 5 teams A,B,C,D & E. Each & every team has 7 players. The team names are located in A3, A12, C3, C12 and E3 columns and its 7 players in (A4-A10, A13-A19, C4-C10..............) in Table sheet.

In Semi Final sheet, through VLOOKUP the semifinalists names appears correctly.

As the tournament has not started yet, I want if Team A/B/C/D/E appears in semifinal, in its following 7 rows, 7 players of this team appears automatically.

It worked for a single formula i.e.
=IF($J$4=Teams!$A$3,Teams!A5,0) (J4 where semifinalist team name appears), it gives the correct result. But when another condition is applied i.e. =IF(SEMIS!J4=Teams!A3,Teams!A4,0),IF(SEMIS!J4=Teams!A12,Teams!A13,0)
it did not work.

Please help.

Suparno

 

[cb12]

I think there are probably far easier ways to achieve your goal rather than a lot of nested IFs however the error in your formula can be corrected as follows

=IF(SEMIS!J4=Teams!A3,Teams!A4,IF(SEMIS!J4=Teams!A13,Teams!A13,0))

 

[suparno]

Thank you very much, it worked.