How to Return Employee Names by Rating Combination in WPS Office Spreadsheets
Question details
The user needs to combine two employee score values into a rating such as Excellent or Good, then list all employee names for each rating on a separate worksheet using dynamic array formulas.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- Employee evaluation data with two scores must be converted into named rating groups and summarized on another worksheet.
- Observed behavior
- The expected result is an automatically spilling list of employee names under each rating category, without manual filtering or copying.
Make sure your worksheet has a column for employee names, the two score cells or columns, and a destination area for rating results. Confirm that WPS Office Spreadsheets supports dynamic array functions such as FILTER and IFS, and save the workbook as .xlsx if you need Excel compatibility.
Create a rating formula and use FILTER to return matching employee names
Use this when you want one dynamic formula per rating category that spills all matching employee names automatically.
The IFS function evaluates score pairs and returns a rating label for each employee. The FILTER function then returns the employee names where the rating equals the target category.
Select the cell where the first employee rating should appear, type =IFS(C3=1,C4=3,"Excellent",C3=2,C4=2,"Good",TRUE,"Other") and press Enter. Adjust the score cell references to match your worksheet, then fill the formula down the rating column.
Select the employee name range, click the Name Box to the left of the formula bar, type EmployeeNames, and press Enter. Repeat for the rating column and name it EmployeeResults.
Select the first output cell on the other worksheet, type =FILTER(EmployeeNames,EmployeeResults="Excellent","") and press Enter. The matching names spill into the cells below automatically.
In the next output block, enter =FILTER(EmployeeNames,EmployeeResults="Good","") and repeat for any other rating labels such as Other.
Use a score-combination lookup table instead of IFS
Use this when many score combinations are easier to maintain in a lookup table, or when the rating logic changes frequently.
Return Employee Names by Rating in WPS Office Spreadsheets
WPS Office Spreadsheets supports dynamic array functions such as IFS and FILTER, so you can rate employees by score combinations and automatically spill matching names across sheets without complex macros.
- 1. Open your workbook in WPS Office Spreadsheets: Launch WPS Office Spreadsheets and open the employee evaluation workbook containing the score data and destination worksheet.
- 2. Add the rating formula: Use =IFS(C3=1,C4=3,"Excellent",C3=2,C4=2,"Good",TRUE,"Other") in the Rating column, or set up a lookup table and return ratings with VLOOKUP.
- 3. Create named ranges: Select the employee names and rating column ranges, then name them EmployeeNames and EmployeeResults through the Name Box.
- 4. Enter FILTER formulas: On the destination sheet, enter =FILTER(EmployeeNames,EmployeeResults="Excellent","") in the first output cell, then repeat for Good and Other.
Frequently Asked Questions
Why does my FILTER formula return #SPILL! when I already have data below?
The spill range needs an empty area. Remove any data or formulas directly below the output cell so WPS Office Spreadsheets can spill the matching names.
How do I handle employees whose score combination is not Excellent or Good?
Add TRUE,"Other" as the final condition in IFS, or add every remaining score combination to your lookup table so each employee receives a valid rating label.
Do these dynamic array formulas work in WPS Office on Windows and Mac?
They work in current WPS Office Spreadsheets versions that support dynamic arrays. Keep WPS Office updated to use IFS, FILTER, and spill behavior.
Why does FILTER show an empty result instead of returning names?
Check that the EmployeeResults named range refers exactly to the rating column and that the rating label in the formula matches the cell value, including no extra spaces.
