How to Filter and Return a Group of Data Using Two Criteria in Excel
Question details
The user needs to return a complete group of records located between two boundary values in Excel 365, identify the start and end positions, rebuild the required headers, and spill the result as a dynamic array.

- Product
- WPS Spreadsheet (Microsoft Excel 365 compatible)
- Device & OS
- not provided
- Scenario
- The user has a named data range and two criteria that mark the start and end of the required group, and needs a formula-based dynamic array result instead of manual filtering.
- Observed behavior
- The desired result should automatically rebuild the required headers and return all matching records between the two criteria as a spilled array.
Confirm that your WPS Office version supports dynamic array functions such as LET, XMATCH, INDEX, SEQUENCE, TAKE, and HSTACK. Name the source range data or update the formula reference to your actual range before entering it.
Return a Full Data Group Between Two Boundary Headers with a Dynamic Array Formula
Use this method when you have a named range and two header criteria that define the start and end of the data group you want to extract.
The formula uses XMATCH to locate the boundary header positions, SEQUENCE to generate the included column positions, INDEX to return the selected columns, and HSTACK to keep the first identifier column. Because it uses dynamic array functions, it spills the result into adjacent cells automatically.
Open your workbook in WPS Spreadsheet, select the source data including headers, then type data in the Name Box and press Enter to create a named range.
In two blank cells, such as J1 and J2, enter the start and end boundary values that match the column headers in the data header row.
Select a blank spill cell, for example H1, and enter: =LET(rng,data,header,TAKE(rng,1),start_col,XMATCH(J1,header),end_col,XMATCH(J2,header),col_idx,SEQUENCE(end_col-start_col+1,,start_col),HSTACK(TAKE(rng,,1),INDEX(rng,SEQUENCE(ROWS(rng)),col_idx))) Press Enter.
Confirm that the headers are rebuilt in the first row of the spill range and that all records between the two boundary criteria are returned automatically.
Return Dynamic Data Groups in WPS Spreadsheet
Use WPS Spreadsheet to build the same Excel 365 dynamic-array formula without needing Microsoft Office. Open your .xlsx workbook, enter the formula, and let WPS spill the matching group between your two criteria.
- 1. Open WPS Spreadsheet: Launch WPS Office, open WPS Spreadsheet, and load the workbook that contains the data you want to filter.
- 2. Name the source range: Select the data including the header row, type data in the Name Box, and press Enter.
- 3. Enter the dynamic array formula: In a blank cell, enter the LET formula from this page and press Enter to spill the matching group.
- 4. Refresh the criteria: Change the start and end criteria cells to any valid header values; the returned group updates automatically.

Frequently Asked Questions
Does WPS Spreadsheet support the Excel 365 functions used in this formula?
Yes, current WPS Office versions support dynamic array functions including LET, XMATCH, INDEX, SEQUENCE, TAKE, and HSTACK. If any function returns #NAME?, update WPS Office through the official WPS site.
Can I use a table instead of a named range?
Yes. Replace data in the formula with the table name or an explicit range such as A1:F100. Make sure the range includes the header row.
What happens if the start or end criteria do not match a header?
XMATCH returns #N/A, so the formula will not spill. Check that the criteria cells exactly match the header labels, including spaces and capitalization.
How can I return the group without the first identifier column?
Remove the HSTACK(TAKE(rng,,1), portion and use INDEX(rng,SEQUENCE(ROWS(rng)),col_idx) directly, or adjust the column index range.
Why does my formula return #SPILL!?
The spill area must be empty. Clear or move cells that block the output range, and WPS will populate the entire returned group automatically.




