Return the Largest Scores Matching a Name Criterion in WPS Office Spreadsheets
Question details
The user needs to extract the highest five scores from a list only when the associated name matches a text criterion, using an Excel-compatible formula.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- A user has a score list with names and wants a dynamic top-five list for names that meet a specific name condition without manually filtering.
- Observed behavior
- The formula should filter scores by the name criterion before ranking them, then return the largest matching scores in descending order.
Confirm the worksheet name, the score column, the name column, and the cell that holds the match criterion. If you use an older formula, prepare to enter it as an array formula with Ctrl+Shift+Enter.
Use FILTER, SORT, and TAKE for Dynamic Array Versions
Use this method when your version of WPS Office Spreadsheets supports dynamic-array functions such as FILTER, SORT, and TAKE.
This method cleans the source list with FILTER, ranks the returned scores with SORT, and keeps only the largest five with TAKE. It updates automatically when the criterion or source data changes.
Click the cell where you want the first result to appear, for example D5.
Type =TAKE(SORT(FILTER('Top CV'!$A$5:$A$24,ISNUMBER(SEARCH($B$3,'Top CV'!$C$5:$C$24))),1,-1),5) and press Enter. Adjust 'Top CV'!$A$5:$A$24 to your score range, 'Top CV'!$C$5:$C$24 to your name range, and $B$3 to the criterion cell.
Confirm that the five largest matching scores appear in descending order and that the formula spills into the adjacent cells automatically.
Use an INDEX/MATCH Array Formula in Older Excel Versions
Use this method when FILTER, SORT, and TAKE are not available and you need to enter the formula as a legacy array formula.
Rank Filtered Scores by Name Criterion in WPS Office Spreadsheets
You can complete this task directly in WPS Office Spreadsheets with Excel-compatible formulas. Enter the criterion and the formula, and WPS Spreadsheets returns the largest matching scores without switching tools.
- 1. Open WPS Office Spreadsheets: Launch WPS Office and open the workbook that contains the score and name data, such as the Top CV sheet.
- 2. Set up the criterion and output cells: Enter the match criterion in cell B3 and reserve an empty area for the top five scores.
- 3. Insert the formula: In the first output cell, enter =TAKE(SORT(FILTER('Top CV'!$A$5:$A$24,ISNUMBER(SEARCH($B$3,'Top CV'!$C$5:$C$24))),1,-1),5) and press Enter. In older versions, use the legacy array formula and press Ctrl+Shift+Enter.
- 4. Review the returned scores: Check that the largest matching scores are listed in descending order and match the name criterion.
Frequently Asked Questions
Why does my TAKE formula return a #SPILL! error?
A #SPILL! error usually means something is blocking the output range. Clear the cells below and to the right of the formula cell, or move the formula to a larger empty area so the five results can spill.
Can I return the matching names next to the top scores?
Yes. With dynamic arrays, use FILTER to return both the score and name columns, then wrap SORT and TAKE around that result. In older versions, use an INDEX/MATCH formula that references the name column.
Does WPS Office support FILTER, SORT, and TAKE?
Recent versions of WPS Office Spreadsheets support many dynamic-array functions. If your version does not include them, use the INDEX/MATCH array formula and press Ctrl+Shift+Enter.
How do I enter an array formula in older WPS Spreadsheets or Excel?
Type the formula, then press Ctrl+Shift+Enter instead of Enter. The formula appears wrapped in curly braces to indicate it is an array formula.
What should I do if two matching scores are equal?
The formulas can return both equal scores. If you need unique records, add a tiebreaker column or extend the COUNTIF condition to distinguish duplicate entries based on another field such as name or row number.
