Data Import & Export

Find Matching Rows by Column Name Across Excel Worksheets

Khadija KhanKhadija Khan Nov 12, 2026 870 views

Question details

Find complete rows across multiple Excel worksheets by locating a consistently named column and filtering values within a specified numeric range.

Find Matching Rows by Column Name Across Excel Worksheets
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.
Before you start

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.

Solution 1Recommended

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.

1
Standardize the headers

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.

2
Create a results worksheet

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.

3
Enter the dynamic array formula

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"))

Combine worksheet ranges with VSTACK and filter by the named column
Adjust the formula for your workbook: Replace Sheet1, Sheet2, Sheet3, A:F, and "Score" with your actual worksheet names, column range, and header. Add more ranges inside VSTACK when you have additional worksheets.
WPS Spreadsheets

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. 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. 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. 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. 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.
Combine rows from multiple worksheets in one results rangeFilter a named numeric column for values from 18.0 to 19.35Work with compatible Excel .xlsx workbook filesUse a familiar spreadsheet interface for formula-based data analysis
microsoft office alternative - wps office

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.