How to Filter One Excel Table Using Multiple Values from Another Table
Question details
The user wants to extract rows from a primary data table based on multiple match criteria listed in a separate reference table.
- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Filtering a large dataset where a specific column needs to match any of the multiple values provided in a secondary list.
- Observed behavior
- Dynamic array functions process the criteria and spill an array of all matching rows from the first table without manual filtering.
Ensure both your main dataset and your criteria list are formatted as official Tables (using Insert > Table) so that any future rows added will automatically update your dynamic formula results. Verify that your spreadsheet software supports dynamic array functions like FILTER.
Use FILTER and COUNTIF Functions
This is the most straightforward and efficient method to return rows from the first table when a column matches any value in the second table.
The FILTER function extracts the data, while COUNTIF acts as a logical test to check if the value in the main table exists within the criteria table.
Click on an empty cell where you want the top-left corner of your filtered results to appear. Ensure there is enough empty space below and to the right for the data to spill.
Type the formula: =FILTER(Table1, COUNTIF(Table2[Filter], Table1[Year]), "No data").
Replace 'Table1' with the name of your main data table, 'Table1[Year]' with the column you are evaluating, and 'Table2[Filter]' with the column containing your multiple criteria.
Press Enter. The formula will automatically spill all matching rows into the adjacent cells.
Use REDUCE, LAMBDA, and VSTACK for Complex Scenarios
Use this advanced array method if you need to process each criterion separately and stack the results, which is useful for highly complex repeated filtering.
Use FILTER Combined with BYROW and TOROW
An alternative logical array approach that cross-references rows and columns to filter matching values.
Efficiently Filter Complex Data with WPS Spreadsheet
WPS Spreadsheet fully supports advanced dynamic array functions like FILTER, COUNTIF, VSTACK, and REDUCE. You can easily cross-reference and extract data across multiple tables without relying on manual filters or macros.
- 1. Open your workbook in WPS Spreadsheet: Launch WPS Office and open the file containing your main table and criteria table.
- 2. Enter the dynamic array formula: Select a blank cell and input the =FILTER combined with COUNTIF formula tailored to your table ranges.
- 3. View instant results: Press Enter to instantly display the spilled array of filtered data, which will update automatically if your criteria change.
Frequently Asked Questions
Why am I getting a #CALC! error when using the FILTER function?
The #CALC! error occurs when the FILTER function finds no matching records and you haven't provided a default value in the [if_empty] argument. Add a text string like "No data" at the very end of your formula (e.g., =FILTER(range, criteria, "No data")) to resolve this.
How do I ensure the formula updates automatically when I add new criteria?
You should format your criteria list as an official Table by selecting the data and pressing Ctrl+T. When you use structured table references (like Table2[Filter]) in your COUNTIF or REDUCE formula, the range will automatically expand to include any new criteria you type at the bottom.
Can I filter based on partial text matches from another table?
Yes, but you need to modify the COUNTIF criteria using wildcards. Instead of an exact match, use wildcards in the COUNTIF function, such as "*"&Table2[Filter]&"*", which allows the formula to search for partial text string matches across your main table.
Does WPS Spreadsheet fully support functions like LAMBDA and VSTACK?
Yes, the latest versions of WPS Office fully support modern dynamic array functions, including FILTER, VSTACK, BYROW, REDUCE, and LAMBDA, ensuring complete compatibility with advanced Excel formulas.
