How to Return Multiple Employees from a Matching Date and Shift Code in WPS Office Spreadsheets
Question details
The user needs an Excel-style array formula to return every employee from a roster whose date matches a selected date and whose shift code is 3.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- A user has a roster sheet with employee names and a date/shift grid and needs to list every employee whose shift code is 3 for the selected date.
- Observed behavior
- A normal lookup returns only the first matching name; the goal is to list all matching employee names in successive rows and show blanks after the final match.
Open the roster workbook and note the exact sheet name plus the ranges containing employee names, date headers, and shift cells. If you want to change the shift code without editing the formula, place the target code in a separate cell before entering the formula.
Use an INDEX-SMALL-IF array formula to return all matching employees
Best for WPS Office Spreadsheets versions that follow traditional Excel array behavior; it returns each matching name in order and hides errors after the last valid match.
This formula filters rows where the header row equals 3 and the corresponding data cell is not empty, then uses SMALL to return the next matching relative row position and INDEX to return the employee name.
Click the cell where the first employee name should appear, for example H2 on your summary worksheet.
Type =IFERROR(INDEX(Sheet1!$D$10:$D$17,SMALL(IF((Sheet1!$E$9:$NN$9=3)*(Sheet1!$E$10:$NN$17<>""),ROW(Sheet1!$E$10:$NN$17)-ROW(Sheet1!$E$10)+1),ROW(A1))),"") and adjust Sheet1 and the ranges to match your workbook.
In traditional WPS Office Spreadsheets versions, press Ctrl+Shift+Enter instead of Enter to confirm the formula.
Drag the fill handle down until you see blank cells, which indicate that there are no more matching employees.
Use the FILTER function if your WPS Office Spreadsheets version supports dynamic arrays
A shorter alternative when dynamic array support is available; it automatically spills all matching employee names into the cells below.
List All Employees Matching a Date and Shift Code in WPS Office Spreadsheets
WPS Office Spreadsheets can run this Excel-style array formula, so you can filter a roster by date and shift code, return every matching employee, and keep working with familiar .xlsx files without changing tools.
- 1. Open WPS Office Spreadsheets: Launch WPS Office and open the employee roster workbook that contains the date and shift data.
- 2. Enter the array formula: Click the first result cell, type the INDEX, SMALL, and IF array formula, and adjust the sheet name and ranges to match your workbook.
- 3. Confirm the formula correctly: Press Ctrl+Shift+Enter if your WPS version uses traditional array formula entry, or press Enter if dynamic array support is enabled.
- 4. Copy the formula down: Drag the fill handle down the result column to list every employee whose shift code matches 3 for the selected date.
Frequently Asked Questions
Can WPS Office Spreadsheets use this Excel array formula?
Yes. WPS Office Spreadsheets supports Excel-compatible array formulas. In versions that follow traditional array behavior, press Ctrl+Shift+Enter after typing the formula so it evaluates correctly.
Why does the formula show a blank instead of an error?
The IFERROR wrapper catches the error that occurs when no more matches are available and returns an empty string, so copied cells beyond the final match look blank.
How do I make the shift code dynamic instead of hard-coded as 3?
Replace the 3 in the condition with a cell reference, for example (Sheet1!$E$9:$NN$9=$B$1), and then enter the target shift code in cell B1.
How do I add a selected date condition to the formula?
Add another logical test inside the IF condition, such as (Sheet1!$A$10:$A$17=$C$1), and multiply it with the existing shift-code and non-blank tests.
How many employees will the formula return?
It returns one employee name per row as you copy the formula down. Copy it until the cells are blank, which means all matching employees have been listed.
