How to Filter Excel Data by Multiple Criteria and Exclude #N/A Errors in WPS Spreadsheets
Question details
The user needs to return associated RFU values from one table by matching the FLASK '# column and exclude any rows that contain #N/A errors in WPS Spreadsheets.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- A workbook contains a source table with RFU values and a criterion table; the user wants to match rows between the two and display only clean, matching RFU records.
- Observed behavior
- Some rows contain #N/A errors, and the existing FILTER approach may return errors or include unwanted rows when criteria and return ranges do not match.
Open the workbook in WPS Spreadsheets and confirm that the source table and criterion table have the same number of rows. Check the exact column name, including special characters such as FLASK '#, before writing the formula.
Use the FILTER Function with CHOOSECOLS to Match Criteria and Exclude #N/A Rows
Best when you need a single dynamic formula in WPS Spreadsheets that returns the associated RFU values and keeps only rows that meet the condition.
The FILTER function evaluates a Boolean include array and keeps only the rows where the condition is TRUE. In this example, CHOOSECOLS(Tabelle1,1,9) returns the first and ninth columns from Tabelle1, while Tabelle1[FLASK '#]=Tabelle2[FLASK '#] matches the criterion table.
If you need more than one condition, multiply the Boolean arrays inside the include argument, for example (Tabelle1[Column1]=Tabelle2[Column1])*(Tabelle1[Column2]=Tabelle2[Column2]).
Select the cell where you want the filtered results to start in WPS Spreadsheets.
Type =FILTER(CHOOSECOLS(Tabelle1,1,9),Tabelle1[FLASK '#]=Tabelle2[FLASK '#]) and adjust the table and column names to match your workbook.
Press Enter. WPS displays the matching RFU values; confirm that rows containing #N/A errors are not included.
Filter the Blue Table Directly with AutoFilter to Hide #N/A Rows
Use this visual method when you only need to review or copy matching rows and do not require a dynamic formula result.
Filter Excel Data by Matching Criteria and Remove #N/A Errors in WPS Spreadsheets
Open the workbook in WPS Spreadsheets and use the FILTER function with CHOOSECOLS to return matching RFU values while excluding #N/A error rows. WPS supports the same formula syntax, so you can complete the task without switching applications.
- 1. Open your workbook: Launch WPS Spreadsheets and open the Excel .xlsx workbook that contains the Tabelle1 and Tabelle2 tables.
- 2. Select the output cell: Click the cell where you want the filtered RFU values to appear.
- 3. Apply the FILTER formula: Type =FILTER(CHOOSECOLS(Tabelle1,1,9),Tabelle1[FLASK '#]=Tabelle2[FLASK '#]) and press Enter.
- 4. Confirm the result: Check that WPS spills the matching records only and that #N/A rows are excluded.
Frequently Asked Questions
Why does my FILTER formula return #VALUE! even though the condition looks correct?
Usually the criteria range and return range are not the same height. In WPS Spreadsheets, make sure the include array and the CHOOSECOLS return array refer to the same number of rows, for example the same table height.
Can I filter by more than one condition in WPS Spreadsheets?
Yes. Multiply the Boolean expressions inside FILTER with an asterisk: =FILTER(CHOOSECOLS(Tabelle1,1,9),(Tabelle1[FLASK '#]=Tabelle2[FLASK '#])*(Tabelle1[Status]="Valid")). Each condition must be enclosed in parentheses.
How do I exclude rows that contain #N/A before using FILTER?
You can wrap the include expression in IFERROR to treat error rows as FALSE, for example: =FILTER(CHOOSECOLS(Tabelle1,1,9),IFERROR(Tabelle1[FLASK '#]=Tabelle2[FLASK '#],FALSE)).
Will this formula work if my table names contain spaces or special characters?
Yes, keep them inside table bracket references, such as Tabelle1[FLASK '#]. If a sheet name contains spaces, wrap it in single quotes, for example 'Project Data'!A2:A100.
