How to Use FILTER with Multiple Conditions and Hide #N/A Errors in WPS Spreadsheets
Question details
The user wants to filter spreadsheet rows by multiple criteria and return a blank cell instead of #N/A when no matching rows exist.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- Working with a spreadsheet that uses the FILTER function to return results based on values in two columns. When no rows match the combined criteria, the formula currently displays #N/A.
- Observed behavior
- FILTER returns matching values correctly, but shows #N/A when the combination of criteria has no matching rows.
Verify that your version of WPS Spreadsheets supports the FILTER function, and check that the referenced columns are the same size before entering the formula.
Wrap FILTER in IFERROR to Display Blank Instead of #N/A
Use this when you want a simple one-cell formula that returns a blank whenever FILTER has no matching rows.
IFERROR catches any error returned by FILTER and replaces it with the value you specify. Using an empty string ("") makes the cell appear blank while keeping the formula intact.
Click the cell where you want the filtered results to appear.
Type =IFERROR(FILTER(Example!M:M,(Example!B:B=$B$5)*(Example!G:G=C5)),"") and press Enter.
Matching rows appear; when no rows match, the cell displays a blank instead of #N/A.
Use IF and ISNA to Replace Only #N/A Errors with Blank
Use this if you prefer an explicit check for #N/A before returning the FILTER result.
Create Error-Free Multiple-Condition Filters in WPS Spreadsheets
Use WPS Spreadsheets to combine FILTER with IFERROR or IF/ISNA, so multi-criteria lookups return matching rows and display a clean blank when no records satisfy the filter.
- 1. Open WPS Spreadsheets: Launch WPS Office and open the workbook that contains the data and criteria cells.
- 2. Select the output cell: Click the cell where you want the filtered list to start.
- 3. Enter the IFERROR FILTER formula: Type =IFERROR(FILTER(Example!M:M,(Example!B:B=$B$5)*(Example!G:G=C5)),"") and press Enter.
- 4. Adjust the references: Replace Example, M:M, B:B, G:G, $B$5, and C5 with your actual sheet name and criteria references.
- 5. Keep Excel compatibility: Save the workbook as .xlsx so it remains compatible with Microsoft Excel.
Frequently Asked Questions
Why does FILTER show #N/A when no rows match?
FILTER returns #N/A by design when no records satisfy the criteria. Wrap it in IFERROR(FILTER(...),"") or IF(ISNA(FILTER(...)),"",FILTER(...)) to display a blank instead.
Can I use FILTER with more than two criteria in WPS Spreadsheets?
Yes. Multiply additional conditions inside the FILTER function, for example: FILTER(Example!M:M,(Example!B:B=$B$5)*(Example!G:G=C5)*(Example!H:H=D5)).
Does IFERROR hide all errors from FILTER?
IFERROR hides any error that FILTER returns, including #N/A, #VALUE!, or #REF!. Use IF(ISNA(...),"",FILTER(...)) if you only want to replace #N/A errors.
Will this formula work in Microsoft Excel?
Yes, FILTER and IFERROR work in Microsoft 365 Excel. WPS Spreadsheets can open and save .xlsx files so the same formula can be used across both applications.
What should I check if my FILTER formula shows #SPILL?
Make sure the cells where the results would spill are empty. Clear any data or formatting that may block the spill range, then re-enter the formula.
