Find Matching Rows by Column Name Across Excel Worksheets
Question details
Find complete rows across multiple Excel worksheets by locating a consistently named column and filtering values within a specified numeric range.

- Product
- Microsoft Excel 365
- Device & OS
- not provided
- Scenario
- The user wants to combine data from worksheets with the same structure and return rows where the target column value is from 18.0 through 19.35.
- Observed behavior
- The desired result is one filtered list containing complete matching rows from all selected worksheets.
Make sure every source worksheet uses the same header names and places the data in the same column order. Remove merged cells from the data ranges and confirm that values in the target column are stored as numbers, not text.
Combine worksheet ranges with VSTACK and filter by the named column
Use this formula-based method when the source worksheets have the same columns and you want a live result that updates as source values change.
This approach stacks the data rows from each worksheet, finds the target column position by its header, and filters the complete rows by the numeric range. Replace the sheet names, data ranges, and target header text to match your workbook.
On each source worksheet, ensure the target field has exactly the same header text, such as "Score", in the header row. Keep the columns in the same order on every sheet.
Select the cell where the combined results should begin, such as Results!A2. In Results!A1, copy or enter the shared header row from one source sheet.
In Results!A2, enter a formula such as: =LET(headers,Sheet1!A1:F1,data,VSTACK(Sheet1!A2:F1000,Sheet2!A2:F1000,Sheet3!A2:F1000),col,XMATCH("Score",headers),FILTER(data,(CHOOSECOLS(data,col)>=18)*(CHOOSECOLS(data,col)<=19.35),"No matching rows"))

Append and filter many worksheets with Excel Power Query
Use Power Query when many worksheets must be combined or when you prefer a refreshable import process instead of maintaining a long formula.
Filter Matching Rows Across Worksheets in WPS Spreadsheets
WPS Spreadsheets can combine matching worksheet ranges and return complete rows based on a shared column header and numeric criteria. Use dynamic array formulas to build a reusable results sheet without writing VBA.
- 1. Check matching worksheet headers: Open each worksheet in WPS Spreadsheets and confirm that the target column header, such as "Score", is identical across all source sheets.
- 2. Add a results worksheet: Click the plus button next to the sheet tabs to create a Results sheet, then copy the shared header row to Results!A1.
- 3. Stack the source data: Select Results!A2 and use VSTACK to combine the data areas, for example: =VSTACK(Sheet1!A2:F1000,Sheet2!A2:F1000,Sheet3!A2:F1000).
- 4. Filter the stacked rows: Extend the formula with XMATCH, CHOOSECOLS, and FILTER to locate the named column and keep only values between 18 and 19.35.

Frequently Asked Questions
Do all worksheets need to use the same column order?
For the VSTACK formula method, the worksheets should use the same columns in the same order. If the order differs, use Power Query or rearrange each source range before stacking it.
How do I change the numeric range in the formula?
Edit the two comparison values in the FILTER condition. For example, change >=18 and <=19.35 to the minimum and maximum values you need.
Why does FILTER return no matching rows when values appear to be in range?
The target values may be stored as text. Select the source column and use Data > Text to Columns > Finish, or multiply the values by 1, then confirm the column is formatted as Number.
Can I add more worksheets to the combined results?
Yes. Add each additional data range as another argument inside VSTACK, such as Sheet4!A2:F1000. In Power Query, add the new table to the Append Queries selection and refresh the query.




