Filter and Combine Matching Data from Two Excel Worksheets
Question details
Combine rows that match a criterion from two worksheets into one result.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- The user wants a dynamic list of matching rows from two worksheets with corresponding columns.
- Observed behavior
- Matching rows from the second worksheet should appear below matching rows from the first worksheet.
Use a version of Excel that supports both FILTER and VSTACK, such as a current version of Microsoft 365 or Excel for the web. Check that the two source ranges have the same number of columns in the same order.
Combine filtered worksheet results with VSTACK
Use this formula to create a dynamic result that updates when source values change.
Open the workbook in Excel and select an empty cell on a worksheet where the combined results have room to spill.
In the selected cell, enter =VSTACK(FILTER(Sheet1!A2:E14,Sheet1!C2:C14="F1L"),FILTER(Sheet2!G2:K14,Sheet2!I2:I14="F1L")) and press Enter.
In the formula bar, replace the sheet names, data ranges, filter columns, and "F1L" with the values used in your workbook. Keep each filter range the same height as its corresponding data range.
Enter or copy the common column headers into the row above the formula. The formula returns data rows only.

Combine matching rows with WPS Spreadsheet filters
Use filters and copy the matching rows from each worksheet into a single list in WPS Spreadsheet. This produces a static result rather than a formula-driven list.
- 1. Open the workbook: Open the Excel workbook in WPS Spreadsheet and add or select a worksheet for the combined list.
- 2. Filter the first sheet: On the first source worksheet, select the table and choose Data > Filter. Open the filter arrow on the relevant column and select the value you want to match.
- 3. Copy the first results: Select and copy the displayed matching rows, then paste them into the output worksheet. Include the headers once.
- 4. Append the second results: Apply the same filter on the second source worksheet, copy its matching data rows without the headers, and paste them below the first set. Check that corresponding columns align.
- 5. Save the combined list: Choose File > Save As and select XLSX if you want to keep the workbook in Excel format.

Frequently Asked Questions
Will the combined Excel result update when source data changes?
Yes. The FILTER and VSTACK formula recalculates when values within its referenced ranges change. Extend the ranges in the formula bar if new rows fall outside them.
Why does the formula show a #CALC! error?
FILTER returns #CALC! when it finds no matches and no empty-result value is specified. Check the criterion and filter column on each worksheet; the example formula expects a match on both sheets.
How do I fix a #SPILL! error?
Select the formula cell and clear any existing content from the cells where Excel needs to display the result. Then check that the output has enough space for all matching rows.
What if the worksheets have columns in a different order?
Rearrange or select the source columns so both arrays use the same order before stacking them. Otherwise, VSTACK will place unrelated fields under the same output columns.
Does the WPS filter-and-copy method update automatically?
No. The pasted rows are a static list. Reapply Data > Filter and copy the matching rows again when the source data changes.




