How to Return All Matching Dates for Each Product in Adjacent Columns in WPS Office Spreadsheets
Question details
The user needs to return the first, second, and subsequent matching dates for each product into adjacent columns in an Excel-compatible workbook.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- A worksheet has product names in column A and dates in column B; column D contains lookup products, and the user wants all dates associated with each product filled across columns from E onward.
- Observed behavior
- A standard lookup only returns the first date, but the user needs every date for a repeated product name to appear in separate adjacent cells.
Check that the product names in column A are exactly consistent with the lookup value in D2 and that the source ranges include all date rows. In older WPS Office or Excel versions, be ready to press Ctrl+Shift+Enter after entering array formulas.
Return All Matching Dates with INDEX, SMALL, and IF
Use this version-compatible array formula when you need the result to work in older WPS Office Spreadsheets and Excel versions without dynamic arrays.
The formula compares the product list in A2:A9 with the lookup value in D2, returns the row numbers of all matches, and uses SMALL with COLUMN(A$1) to produce the first, second, third, and later matches in adjacent cells.
Select cell E2 and enter: =IFERROR(INDEX($B$2:$B$9,SMALL(IF($A$2:$A$9=$D2,ROW($A$2:$A$9)-1),COLUMN(A$1))),"")
In WPS Office Spreadsheets or Excel versions before Microsoft 365/Excel 2021, press Ctrl+Shift+Enter instead of Enter to confirm the formula as an array formula.
Select E2, drag the fill handle to the right to G2, and then drag down through the rows that contain lookup products.
Return All Matching Dates Automatically with FILTER
Use this dynamic-array formula when your version of WPS Office Spreadsheets or Excel supports FILTER and you want all matching dates to spill automatically.
Use WPS Spreadsheets to Return All Matching Dates for Each Product
WPS Office Spreadsheets supports the Excel-compatible INDEX, SMALL, IF, and FILTER formulas, so you can list every matching date by product directly in WPS without changing your workbook structure.
- 1. Open the workbook in WPS Spreadsheets: Launch WPS Office, click Menu > Open, and select the Excel workbook that contains the product and date columns.
- 2. Enter the INDEX, SMALL, and IF formula: Click cell E2, type the formula from Solution 1, and press Ctrl+Shift+Enter if your version requires array formula entry.
- 3. Fill the formula across and down: Drag the fill handle from E2 across to G2 and down through the lookup rows to display all matching dates.
- 4. Save the workbook in Excel format: Click Menu > Save As and choose .xlsx to keep the file compatible with Microsoft Excel.
Frequently Asked Questions
Can I use these formulas in WPS Spreadsheets if my workbook was created in Microsoft Excel?
Yes. WPS Spreadsheets opens .xlsx and .xls files and supports INDEX, SMALL, IF, and FILTER formulas, so you can open the workbook and enter the same formulas in E2.
Why does the formula return #VALUE! or show only the first date?
Older versions require array entry. Select the formula cell, press F2, then press Ctrl+Shift+Enter. Also verify that the product names in column A exactly match the lookup value in D2, with no extra spaces.
What is the difference between the SMALL/IF formula and the FILTER formula?
SMALL/IF works in older versions and must be filled across manually. FILTER is dynamic and spills all matching dates automatically, but it requires Microsoft 365, Excel 2021, or a WPS Office version that supports dynamic arrays.
How can I make the formula update when I add more rows later?
Change fixed ranges such as $A$2:$A$9 to larger ranges or use structured table references, then fill the result formulas down far enough to cover future lookup rows.
