How to Filter Excel Rows Matching Any Value in Another Range
Question details
The user needs to extract specific rows using the FILTER function where a column's values match any text value listed in a separate reference range.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Filtering a dataset dynamically based on a custom list of multiple criteria values stored in a different column.
- Observed behavior
- The user wants the FILTER function to return only the rows containing matches found in the secondary lookup range, simulating a multi-condition COUNTIF.
Ensure your version of Excel or WPS Spreadsheet supports dynamic array functions. Organize your main dataset and your criteria list into clear, separate columns before building the formula.
Use FILTER Combined with COUNTIF
This approach uses COUNTIF to generate a Boolean array (True/False) that tells the FILTER function which rows contain matching values.
By nesting COUNTIF inside the FILTER function's 'include' argument, the spreadsheet checks each row in your target column against your entire list of criteria. If COUNTIF returns a number greater than zero, it acts as a True condition, passing the row to the final output.
Click on an empty cell where you want the filtered data to spill. Ensure there is enough empty space below and to the right.
Type the formula `=FILTER(A2:F100, COUNTIF(H2:H10, F2:F100)>0, "No Match")`.
Change `A2:F100` to your main dataset, `H2:H10` to your range of acceptable criteria values, and `F2:F100` to the specific column being evaluated in your dataset.
Press Enter. The filtered results matching any of the specified values will automatically populate the adjacent cells.
Use FILTER Combined with ISNUMBER and MATCH
An alternative method using ISNUMBER and MATCH, which can be faster and more efficient for processing very large datasets.
Filter Complex Data Easily in WPS Office
WPS Spreadsheet fully supports advanced dynamic array functions like FILTER, COUNTIF, and MATCH, allowing you to seamlessly process complex datasets just like in Microsoft Excel.
- 1. Open your dataset: Launch WPS Spreadsheet and open the document containing your data and criteria ranges.
- 2. Write the formula: Select a blank cell for your output and use the syntax `=FILTER(DataRange, COUNTIF(CriteriaRange, LookupColumn)>0)`.
- 3. View results: Press Enter to instantly extract the rows based on your criteria list.
Frequently Asked Questions
Why does my FILTER formula return a #CALC! error?
The #CALC! error usually appears when the FILTER function finds no matching records. You can avoid this by providing a fallback text value in the optional third argument of the function, such as `=FILTER(range, condition, "No records found")`.
Can I filter matching rows if they contain partial text matches?
Yes, you can use wildcard characters (like asterisks *) within your COUNTIF function, or nest the SEARCH function inside your FILTER formula to match partial text strings from your criteria range.
Does the FILTER function work on older versions of Excel?
No, the FILTER function is a dynamic array formula available only in Microsoft 365, Excel 2021, and modern versions of WPS Office. For older versions, you would need to use the Advanced Filter tool or complex INDEX/AGGREGATE array formulas.
How do I filter based on multiple columns matching the criteria?
You can combine multiple conditions using the plus sign (+) for OR logic or the asterisk (*) for AND logic within the include argument of the FILTER function. For example: `(Condition1)+(Condition2)`.
