How to Filter WPS Spreadsheets Data by a List of Multiple Client Numbers
Question details
The user needs to filter a worksheet by a separate list of multiple client numbers so all required rows are returned in bulk instead of being searched one by one.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- A client worksheet contains data from several departments, and a separate list of clients requires review.
- Observed behavior
- The required client rows are currently searched individually, and a bulk filtering method is needed.
Confirm that the client data range and the separate client-number list are in the same workbook, and verify that the client-number column uses a consistent format. If you plan to use FILTER and XMATCH, make sure your WPS Spreadsheets or Excel version supports dynamic array functions.
Filter Bulk Client Numbers with the FILTER and XMATCH Functions
Use this method when you want a dynamic, formula-based result that updates automatically as the client list changes.
This formula checks each row's client number against the review list and returns only matching rows. You can place the result on the same sheet or a new sheet.
Click an empty cell where you want the filtered client rows to start, such as cell A1 on a new sheet.
Type =FILTER(A2:F100,ISNUMBER(XMATCH(A2:A100,H2:H50)),"No matches") and replace A2:F100 with your client data range, A2:A100 with the client-number column, and H2:H50 with the separate client list.
Press Enter. If dynamic array support is available, the matching rows spill automatically; adjust the ranges if you see #SPILL! or only one row.
Use Advanced Filter to Extract Matching Client Rows
Use this method if you are using an older WPS Spreadsheets or Excel version that does not support dynamic array functions.
Add a COUNTIF Helper Column and Apply Filter
Use this method when you want a simple visual filter and can keep the helper column in the worksheet.
Filter Client Data by a List of Multiple Client Numbers in WPS Spreadsheets
WPS Spreadsheets supports Excel-compatible functions and filter tools, so you can bulk-filter client rows with FILTER and XMATCH, Advanced Filter, or a COUNTIF helper column without switching software.
- 1. Open the client workbook in WPS Spreadsheets: Launch WPS Office, open the client data file, and keep the separate client-number list available in the same workbook.
- 2. Enter the bulk filter formula: Click an empty results cell and enter =FILTER(A2:F100,ISNUMBER(XMATCH(A2:A100,H2:H50)),"No matches") with your actual ranges.
- 3. Press Enter and review results: Press Enter to spill the matching rows, then verify that all required client numbers appear.
- 4. Save as an Excel-compatible workbook: Use File > Save As and choose .xlsx to keep the workbook compatible with Microsoft Excel.
Frequently Asked Questions
Can I filter by a client-number list without using formulas?
Yes. Use Data > Advanced Filter: create a criteria range with the same column header and client numbers below it, then copy the matching rows to another location.
Why does the FILTER formula return #NAME? or #SPILL!?
#NAME? usually means your WPS or Excel version does not support FILTER/XMATCH. Use Advanced Filter or COUNTIF, or update to a current version. #SPILL! means the spill range is blocked by existing data; move the formula to an empty area.
Can the client-number list be on another sheet?
Yes. Reference the sheet name in the formula, for example =FILTER(Data!A2:F100,ISNUMBER(XMATCH(Data!A2:A100,ReviewList!H2:H50)),"No matches").
Will the filtered results update when I change the client list?
Yes, with FILTER and XMATCH results recalculate automatically. Advanced Filter results are static and must be rerun.
Does this method work in Microsoft Excel?
Yes. WPS Spreadsheets uses Excel-compatible formulas and .xlsx files, so the same FILTER/XMATCH, Advanced Filter, and COUNTIF steps work in current Excel versions.
