How to Return All Matching Sales Order Items in WPS Spreadsheets
Question details
The user wants to enter a sales order number and show all matching rows from another worksheet, including item, quantity, and shipping status, because VLOOKUP returns only the first match.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- A sales order dataset contains multiple rows with the same order number. The user needs a lookup method that returns all related rows, not just the first match.
- Observed behavior
- VLOOKUP returns only the first matching sales order item, so later items for the same order are not displayed.
Open the workbook and confirm the source range includes the sales order number, item, quantity, and shipping status columns. Use consistent order-number formatting across both the lookup cell and the source worksheet so matches are not missed.
Use the FILTER Function to Return All Matching Rows
Use this if your WPS Spreadsheets version supports dynamic-array functions; it is the cleanest way to return every matching order row automatically.
The FILTER function asks for the source range that contains the values to return and a condition based on the order number. It spills all matching rows into the selected area and updates when the order number changes.
Click the cell where you want the first returned value to appear.
Type =FILTER(Sheet2!A:D,Sheet2!A:A=A2,"No matches") and press Enter. Replace Sheet2!A:D with your data range, Sheet2!A:A with the order-number column, and A2 with the cell that contains the order number to look up.
Verify that all matching item, quantity, and shipping status columns appear. If no match exists, the formula displays "No matches".
Create a Helper Column to List Matches in Older Versions
Use this when the FILTER function is not available or when you need a method that works without dynamic arrays.
Use a PivotTable to Display All Items for a Selected Order
Use this for an interactive, no-formula way to browse all items for one or more sales orders.
Return All Matching Sales Order Items with WPS Spreadsheets
Use WPS Spreadsheets to replace VLOOKUP with FILTER or a helper-column lookup and display every matching sales order item, quantity, and shipping status without paying for Office 365 dynamic arrays.
- 1. Open your sales order workbook: Launch WPS Spreadsheets and open the .xlsx file that contains your sales order data.
- 2. Locate the source data range: Note the worksheet name and columns that hold the sales order number, item, quantity, and shipping status.
- 3. Enter the FILTER formula: In the output cell, type =FILTER(Sheet2!A:D,Sheet2!A:A=A2,"No matches") and press Enter to return all matching rows.
- 4. Adjust the lookup cell: Change the order-number cell A2 to the order you want to analyze and confirm the results update.
- 5. Save the workbook: Save the file as .xlsx to keep it compatible with Microsoft Excel.
Frequently Asked Questions
Does WPS Spreadsheets support the FILTER function?
Yes, current versions of WPS Spreadsheets support the FILTER function. Open the workbook, click the output cell, and enter a formula such as =FILTER(Sheet2!A:D,Sheet2!A:A=A2,"No matches").
Why does VLOOKUP only return the first sales order match?
VLOOKUP is designed to stop at the first matching value in the first column. To return multiple matches, use FILTER, a helper column with COUNTIF, or a PivotTable.
How do I return item, quantity, and shipping status for all matching order rows?
Include all those columns in the FILTER range. For example, if item is column B, quantity column C, and shipping status column D, return the range Sheet2!A:D together with the order-number condition.
What can I use if the FILTER function is not available?
Use a helper column such as =A2&"-"&COUNTIF($A$2:A2,A2) and then INDEX/MATCH to retrieve the first, second, and later matching rows, or create a PivotTable and filter by order number.
Will this method work when I save the file in Microsoft Excel format?
Yes. Save the workbook as .xlsx to preserve the formulas and results for opening in Microsoft Excel.
