How to Create a Board Game Standings and Matches Table in WPS Spreadsheets
Question details
The user wants to set up a board game league workbook in WPS Spreadsheets with match results, player positions, points, and automatic standings calculations.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- Organizing tournament matches and player standings for a board game league and calculating points automatically.
- Observed behavior
- A normalized match table and scoring table support VLOOKUP/XLOOKUP and SUMIFS/PivotTable summaries without manual point entry.
Prepare the player names, match numbers, finishing positions, and the points awarded for each position before building the workbook. Keep the Match, Player, and Position values consistent because the lookup and summary formulas depend on exact matches.
Build a Normalized Match Table and Scoring Table with Automatic Calculations
Use this solution for a reliable league workbook where points and standings update automatically as you add match results.
Store each player result in its own row instead of one column per player. This structure makes lookup and summary formulas simpler and avoids reshaping data for PivotTables.
Open WPS Office, click Spreadsheets, and create a new blank workbook. Double-click the Sheet1 tab and rename it to Matches.
In the Matches sheet, type Match, Player, Position, and Points in A1:D1. Enter each match-player result in its own row, using the same match number for players in that match.
Click the plus icon next to the sheet tab, rename the new sheet Scoring, and list Position in A1 and Points in B1. Fill row 2 down with each finishing position and its awarded points.
In the Matches sheet, click cell D2 and enter =XLOOKUP(C2,Scoring!$A$2:$A$10,Scoring!$B$2:$B$10). If XLOOKUP is not available, use =VLOOKUP(C2,Scoring!$A$2:$B$10,2,FALSE). Drag the fill handle down column D.
Rename another sheet Standings, list each player in column A, and in B2 enter =SUMIFS(Matches!$D$2:$D$100,Matches!$B$2:$B$100,A2). Select the standings range, click the Data tab, and use Sort to order total points from largest to smallest.
Create a PivotTable Standings Summary Without Writing Summary Formulas
Use this method if you prefer a visual, interactive summary and want WPS Spreadsheets to manage totals and sorting.
Fix Lookup and Summary Formula Errors in the Standings Workbook
Use this troubleshooting solution when VLOOKUP/XLOOKUP returns #N/A or standings totals do not match expected results.
Create Automatic Board Game Standings in WPS Spreadsheets
You can create the same board game league workbook directly in WPS Spreadsheets. Set up the match and scoring tables, apply lookup and SUMIFS formulas or a PivotTable, and WPS will recalculate standings automatically.
- 1. Install WPS Office and open WPS Spreadsheets: Download WPS Office Free from the official WPS site, install it, then open WPS Spreadsheets to start a new blank workbook.
- 2. Set up the match data: Rename Sheet1 to Matches and enter the headers Match, Player, Position, and Points, then add one row per match-player result.
- 3. Add the scoring mapping: Create a Scoring sheet and list each finishing position with the points awarded, such as 1 in A2 and 10 in B2.
- 4. Apply lookup and summary formulas: In the Matches sheet, use VLOOKUP or XLOOKUP to return points, then list players in a Standings sheet and use SUMIFS to total their points.
- 5. Sort and share the standings: Select the standings, click Data > Sort to order by total points descending, then save as .xlsx or use the Menu share option to share it with players.
Frequently Asked Questions
Can I use VLOOKUP instead of XLOOKUP in WPS Spreadsheets?
Yes. In the Points cell use =VLOOKUP(C2,Scoring!$A$2:$B$10,2,FALSE). The position must be in the first column of the scoring table, and the formula returns the points from the second column.
How do I sort standings from highest to lowest in WPS Spreadsheets?
Select the standings range including the header, click the Data tab, choose Sort, set the column to Total Points, and choose Descending. If you use a PivotTable, right-click a row, choose Sort, and sort by the Sum of Points field.
Why does my lookup formula show #N/A?
This usually means the finishing position is not found in the Scoring table. Check that both columns are numbers, remove extra spaces or apostrophes, and make sure every position in the Matches sheet exists in the Scoring sheet.
Can I use different scoring systems for different board games in the same workbook?
Yes. Create a separate scoring sheet for each game or add a Game column to the scoring table. Then use a lookup formula with multiple criteria or filter the PivotTable by game before exporting standings.
