How to Filter Excel Rows Using Multiple Criteria from a Range in WPS Spreadsheets
Question details
The user needs to filter an Excel Table by matching a column against multiple values in a separate criteria range, using dynamic array formulas to return all matching rows without #SPILL! or #CALC! errors.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user has a table such as Table1 and a criteria list such as Criteria[Criteria], and needs to return the header plus every row that matches at least one criterion.
- Observed behavior
- The formula should return the header row and all matching table rows, show a friendly "No matches" message when no rows match, and avoid #SPILL! or #CALC! errors.
Make sure your data is in an Excel Table such as Table1, and that your criteria list is a separate column or table named Criteria. Confirm that the spill area below or beside the formula cell is empty before entering the formula.
Use VSTACK, FILTER, and MMULT to Match Any Criterion from a Range
Best when you want one dynamic formula that returns the table header and all rows matching any value in a criteria column.
This formula works in Excel 365 and in current WPS Spreadsheets versions that support dynamic array functions. The MMULT calculation compares each Name cell against the criteria list and returns a 1 if at least one criterion matches.
Click an empty cell that has enough blank rows and columns around it for the full spill range, so the returned table does not overwrite existing data.
Enter =VSTACK(Table1[#Headers],FILTER(Table1,MMULT(--(Table1[Name]=TRANSPOSE(Criteria[Criteria])),SEQUENCE(ROWS(Criteria[Criteria]),,1,0))>0,"No matches")) and press Enter.
If you see #SPILL!, select the formula cell, identify the blocking cell in the spill range, and delete or move that value so the formula has enough empty cells.
Remove blank cells or unintended zero values from Criteria[Criteria] because they can change the match result or create #CALC! when the list is empty.
Stack Each Matching Set with REDUCE and VSTACK
Use this alternative when you prefer to build the result step by step from each criterion, especially if your version has limited MMULT support.
Return Rows or Values That Do Not Match the Criteria List
Use this when you also need the opposite result: rows whose values do not appear in the criteria list.
Filter Rows by Multiple Criteria in WPS Spreadsheets
WPS Spreadsheets supports Excel-compatible dynamic array formulas, so you can use FILTER, VSTACK, and MMULT to return matching rows and handle no-match results without switching tools.
- 1. Open your workbook in WPS Spreadsheets: Launch WPS Office and open the .xlsx workbook that contains the table and criteria range.
- 2. Select the output cell: Click an empty cell where you want the filtered rows to spill.
- 3. Enter the FILTER formula: Type =VSTACK(Table1[#Headers],FILTER(Table1,MMULT(--(Table1[Name]=TRANSPOSE(Criteria[Criteria])),SEQUENCE(ROWS(Criteria[Criteria]),,1,0))>0,"No matches")) and press Enter.
- 4. Confirm the results: Verify that rows matching any criterion appear. If not, clear the spill area and review the criteria list for blanks or zeros.
Frequently Asked Questions
Why does the formula return #SPILL! in WPS Spreadsheets?
A #SPILL! error means something is already in the cells where the formula wants to output its results. Click the formula cell and look for the spill range marker, then delete or move any values in that range so the formula has enough empty cells.
Why do I get #CALC! when no rows match the criteria?
The #CALC! error occurs when FILTER has no matching rows and no fallback is supplied. Use the third argument of FILTER, such as "No matches", or make sure your criteria list is not empty and contains no blank or zero values.
Can I use a normal criteria range instead of an Excel Table?
Yes. Instead of Criteria[Criteria], use a fixed range such as $G$2:$G$10 in the formula, and lock the reference if you copy the formula. The rest of the MMULT logic stays the same.
How can I return rows that do not match any value in the criteria list?
Use a BYROW and SEARCH formula to test each row against the criteria list. For a non-matching formula, enter =FILTER(Table1,BYROW(Table1[values],LAMBDA(x,NOT(OR(ISNUMBER(SEARCH(x,Table2[Criteria]))))))).
Does this dynamic array method work in Excel and WPS Spreadsheets?
Yes. Current WPS Office versions support Excel-compatible dynamic array functions including FILTER, VSTACK, SEQUENCE, and MMULT. Use the latest version and save the file as .xlsx for the best compatibility.
