Filter an Excel Array Using Values from Another Filtered Array in WPS Spreadsheets
Question details
The user needs to filter a teacher table based on school IDs from another group-filtered list using dynamic array formulas.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- Filtering teacher records so only those whose school ID is in a selected school group are returned.
- Observed behavior
- The user is looking for the correct dynamic array formula to match one array against another filtered array.
Make sure your tables are set up as Excel Tables or named ranges, and confirm that your WPS Spreadsheets version supports dynamic array functions such as LET, XLOOKUP, FILTER, REDUCE, and VSTACK.
Filter teacher records with LET, XLOOKUP, and FILTER
Best for a single dynamic formula that directly returns all teacher rows belonging to a selected school group.
This formula handles multiple matching school IDs without manually copying rows.
Click an empty cell in the worksheet where you want the filtered teacher list to start.
Type =LET(school_in_group,XLOOKUP(School_IDs_in_Teacher_table,School_IDs,School_groups=group),FILTER(Teacher_table,school_in_group)) and press Enter.
Replace School_IDs_in_Teacher_table, School_IDs, School_groups, group, and Teacher_table with your actual worksheet ranges or named ranges.
Combine REDUCE and VSTACK to loop through filtered school IDs
Use this if you need the lookup to iterate through each filtered school ID and stack the matching teacher rows into one table.
Use inverted XMATCH arguments as an include test
Use when you prefer XMATCH and want a compact match test before returning the full teacher records.
Filter Teacher Records by a Grouped School List with WPS Spreadsheets
WPS Spreadsheets can run the same Excel dynamic array logic, so you can filter a teacher table with XLOOKUP and FILTER and get a live spill range without switching tools.
- 1. Open the workbook: Launch WPS Spreadsheets and open the .xlsx workbook that contains your teacher and school data.
- 2. Select an empty spill area: Click a blank cell with enough empty rows and columns for the filtered teacher records.
- 3. Enter the formula: Type =LET(school_in_group,XLOOKUP(School_IDs_in_Teacher_table,School_IDs,School_groups=group),FILTER(Teacher_table,school_in_group)) and press Enter.
- 4. Resolve any spill error: If #SPILL! appears, clear cells blocking the dynamic range or move the formula to a larger empty area.
- 5. Save the workbook: Save the file as .xlsx so the dynamic array formulas remain compatible with WPS Spreadsheets and Microsoft Excel.
Frequently Asked Questions
Why does my FILTER formula return #SPILL! in WPS Spreadsheets?
The #SPILL! error means the dynamic array cannot expand because cells in the spill range are not empty. Clear the blocking cells or move the formula to a larger empty area.
Can I use XLOOKUP as the include argument inside FILTER?
Yes. The recommended formula uses LET to store a logic array from XLOOKUP and passes that array as the include argument to FILTER.
How do I use XMATCH for this filtered lookup?
Invert the XMATCH arguments: =XMATCH(School_IDs_in_Teacher_table,FILTER(School_IDs,School_groups=1),0). Wrap it in ISNUMBER and FILTER to return the full teacher records.
What if my school groups are stored as numbers and the group condition is text?
Ensure both sides use the same data type. Convert text numbers with VALUE() or change the group condition cell to a real number before using the formula.
Does WPS Spreadsheets support REDUCE and VSTACK?
WPS Spreadsheets supports REDUCE and VSTACK in supported versions. Update WPS Office and keep the workbook in .xlsx format for the best compatibility.
