Hello there! I am attempting to make an end of Swiss round report of the Top 8 players that will be playing in the single elimination rounds of a tournament. This would show, for example, the player who is 1st seed, the opponent they played against each round, and the results of each round. I'm trying to make the sheet as simple as possible, since I will not be at the event when it is happening and want to make sure the people who are there can simply drop the data into a Data Input tab and have the formulas do their work. I have sample data from a past event to work with. Here's a what the headers are and some made up data
The Round column is descending from the most recent round to the first round, and the number of matchups in that round varies as players drop from the tournament (either know they won't get prizes, or just need to leave).
The Result column lists either 1WIN, 2WIN, or DRAW, if Player 1 wins, Player 2 wins, or it's a draw respectively.
The table and ID numbers are irrelevant for what I'm trying to do.
Each player is only listed in either Column C or Column E for each round, not both.
I started by using XLOOKUP to check the Top 8 players and see who they played each round. Adding the first seeded player in L2, and "1" in M1 looking at Round 1, I came up with this.
=XLOOKUP($M$1&$L2,A:A&C:C,E:E,(XLOOKUP($M$1&$L2,A:A&E:E,C:C)))
This returned the opponent for the player name in L2 for Round 1, no matter if the L2 name was in column C or E. Then I ran into my issue. I need the result of the matchup, and I can change the formula above to give me the information from the Result column, but I won't know if the player name in L2 was actually Player 1 or Player 2.
=XLOOKUP($M$1&$L2,A:A&C:C,G:G,(XLOOKUP($M$1&$L2,A:A&E:E,G:G)))
Is there a way to get the results to display if the name in L2 won or lost? Is there just a better way to go about this?
| Round | Table | Player 1 Name | Player 1 ID | Player 2 Name | Player 2 ID | Result |
| 6 | 465 | Ashley | #### | Bob | #### | 1WIN |
The Round column is descending from the most recent round to the first round, and the number of matchups in that round varies as players drop from the tournament (either know they won't get prizes, or just need to leave).
The Result column lists either 1WIN, 2WIN, or DRAW, if Player 1 wins, Player 2 wins, or it's a draw respectively.
The table and ID numbers are irrelevant for what I'm trying to do.
Each player is only listed in either Column C or Column E for each round, not both.
I started by using XLOOKUP to check the Top 8 players and see who they played each round. Adding the first seeded player in L2, and "1" in M1 looking at Round 1, I came up with this.
=XLOOKUP($M$1&$L2,A:A&C:C,E:E,(XLOOKUP($M$1&$L2,A:A&E:E,C:C)))
This returned the opponent for the player name in L2 for Round 1, no matter if the L2 name was in column C or E. Then I ran into my issue. I need the result of the matchup, and I can change the formula above to give me the information from the Result column, but I won't know if the player name in L2 was actually Player 1 or Player 2.
=XLOOKUP($M$1&$L2,A:A&C:C,G:G,(XLOOKUP($M$1&$L2,A:A&E:E,G:G)))
Is there a way to get the results to display if the name in L2 won or lost? Is there just a better way to go about this?
