How to Filter a Dynamic List for Missing Values in WPS Spreadsheets
Question details
The user needs to identify which values are missing from a dynamic list generated by combining multiple tables with VSTACK, using a formula that works when the arrays have different lengths.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user is working with a spilled dynamic array created from multiple stacked tables and comparing its unique values against a lookup list.
- Observed behavior
- The goal is to return the missing items, or the text "All" when no items are missing.
Make sure your WPS Office version supports dynamic array functions such as VSTACK, CHOOSECOLS, UNIQUE, FILTER, and XMATCH, and that both the dynamic list and lookup list are visible in your workbook.
Use LET, UNIQUE, CHOOSECOLS, FILTER, and XMATCH to return missing values
Use this when you have a spilled dynamic list and want a compact formula that compares unique items with a lookup list and returns only missing values or "All".
The formula first extracts the relevant column from the spilled range with CHOOSECOLS, reduces it to unique values with UNIQUE, then uses XMATCH to test each value against the lookup list. FILTER returns the values where XMATCH produces an error, meaning they are missing.
Click an empty cell where the spilled missing-values list should start.
Type =LET(arrange,UNIQUE(CHOOSECOLS(B14#,2)),FILTER(arrange,ISERROR(XMATCH(arrange,L6#)),"All")) and replace B14# with your VSTACK spill range, the column index 2 with the column to compare, and L6# with your lookup list spill reference.
Press Enter. WPS Spreadsheets spills the missing values into the cells below, or returns All when nothing is missing.
Create a dynamic summary matrix with REDUCE and LAMBDA
Use this if you also need a per-participant summary table showing totals and a marker for each unique value, not only the missing items.
Find Missing Values in a Dynamic List with WPS Spreadsheets
Use WPS Spreadsheets dynamic array functions to combine tables with VSTACK and compare unique values with a lookup list using LET, UNIQUE, CHOOSECOLS, FILTER, and XMATCH. The result spills automatically and updates as data changes.
- 1. Download and open WPS Office: Download WPS Office Free from wps.com and open your workbook in WPS Spreadsheets.
- 2. Prepare the dynamic list: Use =VSTACK(t_arr1,t_arr2,t_arr3) or your existing spilled range to combine the source tables.
- 3. Enter the missing values formula: In an empty cell, enter =LET(arrange,UNIQUE(CHOOSECOLS(B14#,2)),FILTER(arrange,ISERROR(XMATCH(arrange,L6#)),"All")) and adjust B14#, the column number, and L6# to match your workbook.
- 4. Verify the spilled result: Press Enter and confirm the missing items spill into the adjacent cells, or the cell shows All when no values are missing.
Frequently Asked Questions
Does this formula work if the lookup list is on another sheet?
Yes. Replace L6# with a reference such as Sheet2!L6# or the specific lookup range. As long as the reference returns a spilled range or a valid range, XMATCH can compare against it.
What does the # symbol mean in B14# and L6#?
The # reference means the entire spilled array range starting at that cell. It lets formulas automatically include all rows returned by VSTACK, FILTER, or another dynamic array formula.
Why does my formula return #SPILL! instead of the missing values?
The spill area likely contains existing data or merged cells. Clear the cells below and to the right of the formula, or move the formula to a location with enough empty space.
How do I show a blank instead of "All" when nothing is missing?
Replace the last argument of FILTER with an empty string: =LET(arrange,UNIQUE(CHOOSECOLS(B14#,2)),FILTER(arrange,ISERROR(XMATCH(arrange,L6#)),"")).
Can I compare values in a different column?
Yes. Change the 2 in CHOOSECOLS(B14#,2) to the column position you want to compare, counted from the spilled range.
