Return Serial Numbers Without an Associated Date in Excel
Question details
The user needs a formula to return serial numbers from one column when the adjacent date column is blank, using either a dynamic array formula or a legacy array formula.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- A worksheet has serial numbers in one column and associated dates in the adjacent column. Some date cells are empty, and the goal is to extract the serial numbers that do not yet have a date.
- Observed behavior
- Rows where the date cell is blank should be returned as a list of serial numbers, while rows with nonblank date cells and blank serial number cells must be excluded.
Make sure the serial numbers and dates are in adjacent columns with the same row range, for example A2:A100 and B2:B100. For older WPS Office or Excel versions without FILTER, plan to enter the legacy formula with Ctrl+Shift+Enter.
Use the FILTER dynamic array formula
Best for WPS Office and Excel versions that support dynamic arrays because it returns all matching serial numbers automatically.
This method uses the FILTER function to test two conditions at once: the serial number cell is not blank, and the adjacent date cell is blank. The result spills down from a single cell.
Click the empty cell where you want the returned list to begin, such as D2.
Type =FILTER(A2:A100,(A2:A100<>"")*(B2:B100=""),"") and press Enter.
The formula returns all nonblank serial numbers from A2:A100 whose corresponding date cells in B2:B100 are blank.
Use the INDEX, SMALL, and IF legacy array formula
Use this method in older WPS Office or Excel versions that do not support the FILTER function.
Extract Serial Numbers with Blank Dates in WPS Office Spreadsheets
Use WPS Office Spreadsheets to apply the FILTER dynamic array formula or the compatible INDEX, SMALL, and IF legacy array formula. It handles the same Excel workbooks and formulas while keeping the workflow familiar.
- 1. Open your workbook in WPS Office Spreadsheets: Launch WPS Office, open the .xlsx file containing the serial numbers and blank date column.
- 2. Click the result cell: Select an empty cell where the returned serial number list should begin, such as D2.
- 3. Enter the recommended formula: Type =FILTER(A2:A100,(A2:A100<>"")*(B2:B100=""),"") and press Enter. If FILTER is not available, use the INDEX, SMALL, and IF legacy formula and press Ctrl+Shift+Enter.
- 4. Review and save the workbook: Confirm that only serial numbers with blank adjacent date cells are returned, then save the file as an Excel-compatible workbook.
Frequently Asked Questions
Does WPS Office support the FILTER function for this task?
Recent versions of WPS Office Spreadsheets support FILTER and dynamic array formulas. If your version does not have FILTER, use the INDEX, SMALL, and IF array formula and confirm it with Ctrl+Shift+Enter.
How do I exclude blank serial numbers from the returned list?
Include the condition (A2:A100<>"") in the FILTER formula. This checks that the serial number cell is not blank before the formula returns it.
Why must I press Ctrl+Shift+Enter for the legacy formula?
Legacy array formulas need to be evaluated as arrays. Select the cell, type the formula, and press Ctrl+Shift+Enter; WPS or Excel then displays curly braces around the formula.
What should I check if my INDEX, SMALL, and IF formula returns no results?
Check that the ranges match your data, the date cells are truly blank rather than containing spaces, and the row offset ROW($B$2:$B$13)-1 matches the first row of the range.
