How to Use an Excel Formula to List People Due for Assessments by Month in WPS Spreadsheets
Question details
List employee names due for assessments in each month by comparing multiple due-date columns with month headings and extracting matching records.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user has a sheet of employees with several assessment due-date columns and wants to return all employees due in each month in a separate summary layout.
- Observed behavior
- The user needs a reusable formula that can be filled across month columns and down rows without manual filtering.
Confirm that your names are in Sheet1!B3:B500, your six assessment due-date columns are in Sheet1!G3:L500, and your first month heading is in Sheet2!B3. If you are using an older version of WPS Spreadsheets, be ready to confirm array formulas with Ctrl+Shift+Enter.
Return Matching Employee Names by Month with an INDEX, SMALL, IF and MMULT Array Formula
Use this formula when you need to compare up to six due-date columns against a month heading and extract every matching employee name dynamically.
This formula uses MMULT to check whether any of the six assessment date columns equals the target month, then INDEX returns the corresponding names. It works in WPS Spreadsheets and can be filled across a 12-month summary range.
In WPS Spreadsheets, open the workbook and select the first result cell below the first month heading, for example Sheet2!B4.
Type =IFERROR(INDEX(Sheet1!$B$3:$B$500,SMALL(IF(MMULT(N(Sheet1!$G$3:$L$500=Sheet2!B$3),ROW($1:$6)^0)=1,ROW($1:$498)),ROW(1:1))),"") and do not press Enter yet.
For older WPS Spreadsheets versions, press Ctrl+Shift+Enter to confirm it as an array formula. In Microsoft 365-compatible versions, press Enter normally.
Drag the fill handle from Sheet2!B4 across all 12 month columns and down enough rows to display multiple matching names.
Select the month heading cells, right-click, choose Format Cells, and set a custom date format such as mmmm so the comparison values display as month names.
Create a Due-Assessment Month Tracker in WPS Spreadsheets
WPS Spreadsheets supports the same INDEX, SMALL, IF, and MMULT array formulas used in Microsoft Excel, so you can build an automated due-assessment tracker and fill it across months without manual filtering.
- 1. Download and install WPS Office: Download WPS Office Free from the official WPS site and install WPS Spreadsheets on your Windows or Mac computer.
- 2. Open your existing Excel workbook: Launch WPS Spreadsheets, click Menu > Open, and select your .xlsx assessment workbook to keep the existing data and formulas intact.
- 3. Apply the array formula: Select the first summary result cell, paste the INDEX/MMULT formula, and confirm it with Ctrl+Shift+Enter or Enter depending on your WPS version.
- 4. Fill and format the summary grid: Drag the formula across the 12 month headings and down the rows, then format the month headings as mmmm and check the returned names.
Frequently Asked Questions
Why does my formula return #VALUE! instead of employee names?
This usually happens when the array formula is entered normally in an older version. Select the formula cell, press F2, and confirm with Ctrl+Shift+Enter. Also check that the ranges Sheet1!$B$3:$B$500 and Sheet1!$G$3:$L$500 cover all data rows.
Can I use month names such as January in the month headings?
Yes. Make sure the heading cells contain real dates, then select them, right-click, choose Format Cells, and apply the custom format mmmm. The formula compares the underlying date values, so typed text such as "January" may not match.
How do I show multiple employees due in the same month?
After entering the formula in the first result cell, drag it down enough rows. The SMALL function returns the first, second, third, and subsequent matching row positions until no more matches exist, when IFERROR returns an empty string.
Does the formula work in WPS Spreadsheets on Windows and Mac?
Yes. WPS Spreadsheets supports the INDEX, SMALL, IF, and MMULT functions. On Windows, you can confirm the array formula with Ctrl+Shift+Enter; on Mac, use Command+Shift+Enter if your version requires array confirmation.
