Function Problems

How to Filter and Return a Group of Data Using Two Criteria in Excel

Algirdas JasaitisAlgirdas Jasaitis Nov 13, 2026 870 views

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.

How to Filter and Return a Group of Data Using Two Criteria in Excel
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.
Before you start

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.

Solution 1Recommended

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.

1
Open the WPS spreadsheet and name the source range

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.

2
Add cells for the start and end criteria

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.

3
Enter the dynamic array formula

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.

4
Check the spilled result

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.

Formula returned the matching group: If WPS Spreadsheet shows #NAME? or #SPILL!, check that the function names are available in your version and that the spill area is empty.
WPS Spreadsheet

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. 1. Open WPS Spreadsheet: Launch WPS Office, open WPS Spreadsheet, and load the workbook that contains the data you want to filter.
  2. 2. Name the source range: Select the data including the header row, type data in the Name Box, and press Enter.
  3. 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. 4. Refresh the criteria: Change the start and end criteria cells to any valid header values; the returned group updates automatically.
Filters and returns a complete data group between two boundary criteria in a single formulaSupports Excel 365-style functions including LET, XMATCH, INDEX, SEQUENCE, TAKE, and HSTACKOpens, edits, and saves .xlsx files with full Microsoft Excel format compatibilityFree, lightweight, and familiar Office interface with no layout relearningDynamic array results update automatically when criteria change
microsoft office alternative - wps office

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.