WPS Spreadsheet Formula to Rank School Results and Return the Nth Position
Question details
The user needs a formula that selects one school, sorts its finishing results, returns the nth-lowest position, and identifies the related overall position.
- Product
- WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- A worksheet contains multiple runners for each school, with only the top four runners potentially included in the team result.
- Observed behavior
- A reliable formula cannot be finalized because the worksheet columns, selected-school cell, meaning of nth position, and top-four scoring rule are not specified.
Confirm which columns contain the school name, finishing position, and overall position. Also decide whether n can exceed four or whether the formula must limit each school to its four best finishing positions.
Use FILTER, SORT, SMALL, and XLOOKUP for a selected school
Use this flexible template when the sheet has separate columns for school, finishing position, and the corresponding overall position.
Assume column A contains School, column B contains Finishing Position, column C contains Overall Position, H2 contains the selected school, and H3 contains the requested n value. The formula returns the overall position associated with the nth-lowest finishing position.
If finishing positions can repeat, use a unique runner or result ID as an additional sort and lookup key so that XLOOKUP does not select the wrong duplicate row.
Enter the school to analyze in H2 and the requested position number, such as 1, 2, or 3, in H3. Keep the data ranges aligned, for example A2:A100, B2:B100, and C2:C100.
In an empty result cell, select the cell and enter =LET(positions,FILTER($B$2:$B$100,$A$2:$A$100=$H$2),IFERROR(SMALL(positions,$H$3),"Not enough results")). This returns the selected school's nth-lowest finishing position.
To return the related value from column C, enter =LET(positions,FILTER($B$2:$B$100,$A$2:$A$100=$H$2),overall,FILTER($C$2:$C$100,$A$2:$A$100=$H$2),nth,IFERROR(SMALL(positions,$H$3),NA()),IFERROR(XLOOKUP(nth,positions,overall),"Not found")).
Filter column A to the school in H2, sort its finishing positions from smallest to largest, and verify that the formula's nth value and corresponding overall position match the intended runner.
Limit the calculation to each school's top four runners
Use this variation when only the four best finishing positions count toward the school result.
Use helper columns when dynamic-array functions are unavailable
Use helper columns for older spreadsheet versions or when a transparent, easy-to-audit calculation is preferred.
Rank school results and retrieve the matching position in WPS Spreadsheet
WPS Spreadsheet can filter runners by school, sort their finishing positions, limit the calculation to the top four, and use SMALL, XLOOKUP, or INDEX to return the requested result. Its familiar formula interface makes it easy to test the calculation against an XLSX worksheet.
- 1. Open the worksheet: Launch WPS Office, choose Spreadsheet, and open the school-results XLSX file from the Home screen or File > Open.
- 2. Identify the source columns: Locate the columns for School, Finishing Position, and Overall Position, then choose cells for the selected school and n value.
- 3. Enter the ranking formula: Select an empty result cell and enter the FILTER, SORT, SMALL, and XLOOKUP template using the actual ranges in the worksheet.
- 4. Apply the top-four rule if needed: Wrap the sorted filtered results with TAKE(...,4) or use the top-four formula before returning the nth result.
- 5. Verify and save the result: Compare the returned value with the sorted source rows, then choose File > Save or Save As to preserve the completed workbook.
Frequently Asked Questions
What information is needed before writing the exact school-ranking formula?
Provide the column letters or sample headers for school, finishing position, and overall position, plus the cell containing the selected school and the meaning of n. Confirm whether only the top four runners count.
How do I return the nth-lowest result for one school in WPS Spreadsheet?
Filter the finishing-position range by the selected school and apply SMALL, such as =SMALL(FILTER(B2:B100,A2:A100=H2),H3), where H2 contains the school and H3 contains n.
How can I restrict the calculation to the top four runners?
Sort the filtered results and keep the first four with TAKE, then apply SMALL. For example, use =LET(p,SORT(FILTER(B2:B100,A2:A100=H2)),SMALL(TAKE(p,MIN(4,ROWS(p))),H3)).
Why might XLOOKUP return the wrong overall position?
If two runners share the same finishing position, XLOOKUP returns the first matching row. Add a unique runner ID or use a sorted multi-column range to distinguish tied results.
What happens if the selected school has fewer results than the requested n value?
SMALL returns an error because the requested position does not exist. Wrap the formula with IFERROR to display a message such as "Not enough results" instead.
