Data Import & Export

Filter and Combine Matching Data from Two Excel Worksheets

Phi Hung VoPhi Hung Vo Nov 18, 2026 869 views

Question details

Combine rows that match a criterion from two worksheets into one result.

Filter and Combine Matching Data from Two Excel Worksheets
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.
Before you start

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.

Solution 1Recommended

Combine filtered worksheet results with VSTACK

Use this formula to create a dynamic result that updates when source values change.

1
Choose an output cell

Open the workbook in Excel and select an empty cell on a worksheet where the combined results have room to spill.

2
Enter the formula

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.

3
Adjust the references

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.

4
Add headers if needed

Enter or copy the common column headers into the row above the formula. The formula returns data rows only.

Combine filtered worksheet results with VSTACK
Check for matches and spill space: This formula expects at least one match on each worksheet; otherwise, FILTER returns a #CALC! error. If Excel shows #SPILL!, clear cells blocking the output area.
Combine worksheet data in WPS

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. 1. Open the workbook: Open the Excel workbook in WPS Spreadsheet and add or select a worksheet for the combined list.
  2. 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. 3. Copy the first results: Select and copy the displayed matching rows, then paste them into the output worksheet. Include the headers once.
  4. 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. 5. Save the combined list: Choose File > Save As and select XLSX if you want to keep the workbook in Excel format.
Filter and combine matching rows from multiple worksheetsOpen and save Microsoft Excel XLSX workbooksUse familiar spreadsheet menus to organize dataEdit the combined list without changing the source rows
microsoft office alternative - wps office

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.