How to Filter Excel Rows by a List of Values | WPS Spreadsheet
Question details
The user needs to filter a main data table so that only the rows matching a separate list of countries are returned, displaying their associated population and capital data.
- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Extracting specific records from a large dataset based on multiple criteria provided in a separate lookup list.
- Observed behavior
- The user wants to dynamically retrieve data for specific items without manually selecting them one by one in the standard filter dropdown.
Ensure your main data table and criteria list are organized in separate columns, and verify that there are no extra spaces in your text fields that could prevent an exact match.
Use the FILTER and MATCH Functions
Combine the FILTER and MATCH functions to dynamically extract rows that match any value in your criteria list.
The FILTER function extracts the desired records dynamically. By nesting the MATCH function wrapped in ISNUMBER, you create a logical test that checks if the row's country exists in your separate criteria list.
Click on an empty cell where you want the top-left corner of your filtered results to appear.
Type the following formula into the formula bar: =FILTER(C4:E11,ISNUMBER(MATCH(C4:C11,H4:H7,0)),"No matches")
Replace 'C4:E11' with the range of your main data table. Replace 'C4:C11' with the specific column in the main table containing the country names. Finally, replace 'H4:H7' with the range of your separate criteria list.
Press Enter. The formula will automatically spill the filtered results, including the country, population, and capital, into the adjacent cells.
Use the Advanced Filter Feature
Use the built-in Advanced Filter tool to extract data to a new location based on a criteria range without using complex formulas.
Filter and Analyze Data Easily with WPS Spreadsheet
WPS Spreadsheet fully supports dynamic array formulas like FILTER, ISNUMBER, and MATCH. It provides powerful data analysis capabilities, enabling you to extract and manipulate information across large datasets with ease.
- 1. Open your file in WPS Office: Launch WPS Spreadsheet and open the workbook containing your dataset.
- 2. Organize your data: Ensure your main table and the list of items you want to filter by are clearly separated.
- 3. Apply the FILTER formula: Enter the =FILTER() combined with ISNUMBER(MATCH()) formula into an empty cell.
- 4. View your results: Press Enter to instantly display the filtered dataset, updating automatically if your criteria list changes.
Frequently Asked Questions
Why does my FILTER formula return a #SPILL! error?
This error occurs when the dynamic array function does not have enough blank space to output the results. Check the cells below and to the right of your formula and clear any obstructing data.
How can I filter by multiple criteria lists simultaneously?
You can multiply the ISNUMBER(MATCH()) logic tests within the FILTER function's 'include' argument. For example, using (ISNUMBER(MATCH(range1,list1,0))) * (ISNUMBER(MATCH(range2,list2,0))) will require both conditions to be met.
Is the FILTER function available in older spreadsheet software?
The FILTER function is a dynamic array feature available in modern spreadsheet applications, including the latest versions of WPS Spreadsheet and Excel 365. If you are using an older version, you must use the 'Advanced Filter' tool or complex INDEX/MATCH array formulas.
