WPS Spreadsheet: Find Values Common to Four Excel Tables
Question details
The user needs a dynamic array formula that returns only the values appearing in all four Excel tables.
- Product
- WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- The workbook contains four tables with related value columns, and the user wants to compare them and spill the shared values into a result range.
- Observed behavior
- The desired result is a spilled list containing values present in every source table, optionally with duplicates removed.
Confirm that the four source ranges are formatted as tables and note their exact table and column names. Make sure the result area has enough empty cells for the dynamic array to spill.
Use FILTER with COUNTIF conditions across all four tables
Use this dynamic array solution when you want to return values from one table only when they also occur in the other three tables.
The formula tests each candidate value against the other three tables. Multiplying the COUNTIF results creates an AND condition, so FILTER keeps only values found in every source. Wrap the result in UNIQUE if the candidate column can contain duplicates.
Open the worksheet in WPS Spreadsheet and select the cell where the shared-value list should begin.
Enter =FILTER(Table7[rnti-BIP],(COUNTIF(Table1[[CRNTI]:[CRNTI3]],Table7[rnti-BIP])>0)*(COUNTIF(Table4[rnti-UL-TTI],Table7[rnti-BIP])>0)*(COUNTIF(Table5[rnti-DL-TTI],Table7[rnti-BIP])>0),"") and replace the table or column names with the names in your workbook.
Press Enter. WPS Spreadsheet should spill the matching values into the cells below the formula.
To return each shared value once, wrap the formula in UNIQUE: =UNIQUE(FILTER(Table7[rnti-BIP],(COUNTIF(Table1[[CRNTI]:[CRNTI3]],Table7[rnti-BIP])>0)*(COUNTIF(Table4[rnti-UL-TTI],Table7[rnti-BIP])>0)*(COUNTIF(Table5[rnti-DL-TTI],Table7[rnti-BIP])>0),""))
Use a normalized list before comparing values
Use this alternative when the source tables contain repeated entries and you want to inspect a consolidated, unique list before applying additional comparisons.
Compare Excel tables with dynamic formulas in WPS Spreadsheet
WPS Spreadsheet can use FILTER, COUNTIF, UNIQUE, VSTACK, and other dynamic array functions to compare table columns and return shared values. Its familiar formula interface makes it easy to open an Excel workbook, adjust structured references, and generate a spill-based result without manual filtering.
- 1. Open the workbook: Launch WPS Spreadsheet, choose Open from the Home tab, and select the Excel .xlsx workbook containing the four tables.
- 2. Check table references: Click a table and review its table and column names, then update the FILTER and COUNTIF references to match the workbook.
- 3. Enter the formula: Select an empty result cell and enter the dynamic FILTER formula that checks each candidate against the other three tables.
- 4. Confirm the spilled results: Press Enter and review the values that spill below the formula; use UNIQUE around FILTER when only one instance of each value is needed.
Frequently Asked Questions
How do I return values that appear in all four tables?
Use FILTER on one table's value column and multiply COUNTIF tests for the other three tables. For the example references, use =FILTER(Table7[rnti-BIP],(COUNTIF(Table1[[CRNTI]:[CRNTI3]],Table7[rnti-BIP])>0)*(COUNTIF(Table4[rnti-UL-TTI],Table7[rnti-BIP])>0)*(COUNTIF(Table5[rnti-DL-TTI],Table7[rnti-BIP])>0),"").
How can I remove duplicate common values?
Wrap the FILTER formula in UNIQUE: =UNIQUE(FILTER(...)). This returns each value found in all four tables only once.
Why does the formula return a blank cell?
The formula returns a blank when no candidate value exists in every table. Also verify that the structured references, table names, and column names exactly match the workbook.
Can I compare several columns in one table?
Yes. Use a multi-column structured reference such as Table1[[CRNTI]:[CRNTI3]] inside COUNTIF. This checks the candidate against all columns in that table.
What should I do if dynamic arrays do not spill?
Clear cells below and beside the formula so the result range is empty, then re-enter the formula. If the function is unavailable, update WPS Office and verify that the workbook is using supported FILTER and COUNTIF functions.
