How to Filter and Return Entire Rows from an Excel Table in WPS Office Spreadsheets
Question details
The user needs to return complete table rows from an Excel table when multiple matching row numbers are stored in an array, using dynamic-array formulas that spill multi-column results correctly.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- Working with an Excel table and dynamic array formulas where matching row positions are stored in a spill range or array.
- Observed behavior
- Dynamic-array formulas such as INDEX and MAP do not always spill multi-column rows as expected when row numbers are stored in an array, so a Boolean FILTER include array is required to return the entire matching rows.
Make sure your version of WPS Office Spreadsheets supports dynamic array functions such as FILTER, SEQUENCE, MATCH, and ISNUMBER. Arrange the source table with its header row so the row-number calculation can be offset correctly.
Use FILTER with a Boolean include array to return entire rows
Use this when you already have matching row numbers stored in an array and need FILTER to spill all columns for every matched row.
FILTER can return multiple columns at once, which makes it more reliable than INDEX or MAP when the include condition is based on an array of row numbers. The Boolean include array must match the number of rows in the source range.
Click the first cell where you want the returned rows to appear, such as G2.
In the formula bar, use ROW or SEQUENCE to generate source row numbers, for example ROW(B2:B19)-1.
Use MATCH to test which row numbers are present in the stored array, for example MATCH(A2:A19,B2:B19,0).
Type =FILTER(D2:F19,ISNUMBER(MATCH(ROW(B2:B19)-1,Rw,0))) into the formula bar.
Press Enter and confirm that FILTER returns the complete matching rows from D2:F19.
Use a helper table for complex text-based criteria across many columns
Use this when the include condition depends on text matches across several columns and a direct FILTER is hard to maintain.
Return Entire Matching Rows with FILTER in WPS Office Spreadsheets
Use the FILTER function in WPS Office Spreadsheets to build a Boolean include array and return complete rows from a table. It supports the same .xlsx dynamic-array formulas as Excel, so your matching-row reports can stay in one workbook without extra tools.
- 1. Open your workbook in WPS Office Spreadsheets: Launch WPS Office Spreadsheets and open the Excel workbook containing the source table.
- 2. Select the spill start cell: Click the first cell where the returned rows should appear.
- 3. Enter the FILTER formula: Type =FILTER(D2:F19,ISNUMBER(MATCH(ROW(B2:B19)-1,Rw,0))) in the formula bar.
- 4. Press Enter and confirm the spill: Press Enter to return all matching rows and verify the multi-column spill range.
Frequently Asked Questions
Why does FILTER return entire rows when INDEX only returns one value?
FILTER accepts a Boolean include array and returns every column in the source range, while INDEX returns the value at a single row and column intersection unless it is combined with other functions.
What should I do when the row number array is stored in a named range?
Reference the named range directly in the include argument, for example =FILTER(D2:F19,ISNUMBER(MATCH(ROW(B2:B19)-1,Rw,0))).
Do I need to adjust row numbers if my table starts on row 2?
Yes. Subtract the header offset, such as ROW(B2:B19)-1, so the generated sequence matches the source table position.
Can WPS Office open and edit this formula from an Excel file?
Yes. WPS Office Spreadsheets supports .xlsx dynamic array functions including FILTER, SEQUENCE, MATCH, and ISNUMBER.
What can I use if the criteria are text-based across many columns?
Use a helper table or Power Query to precompute an Include=1 column, then use FILTER or TRANSPOSE(FILTER(...)) to return the matching results.
