Function Problems

How to Use Excel FILTER Formula to Copy Data When Another Column Matches

Maira Mehtab Sep 22, 2026 873 views

Question details

The user needs a formula to return values from column B to another worksheet based on a specific text condition in column F, while excluding blank results and preventing #CALC! errors.

Product: Excel
Device & OS: not provided
Scenario: Filtering and dynamically copying data rows across worksheets based on text criteria.
Observed behavior: The current formula successfully pulls the required data but inputs a row with a #CALC! error for rows that do not meet the requirement or when no matching data exists.

Before you start

Ensure you are using a version of Excel or WPS Office that supports dynamic array functions, as the FILTER function is unavailable in older standalone versions.

Solution 1Recommended

Use the FILTER Function with Multiple Conditions and an Empty String Argument

Apply a dynamic array formula that evaluates multiple criteria and handles empty results to seamlessly extract data without encountering errors.

The FILTER function can process multiple criteria by multiplying logical arrays together. To prevent the #CALC! error when no records match your criteria, you must define the third argument of the formula (if_empty) with an empty string.

Select the destination cell

Open your target worksheet and click on the specific cell where you want the filtered data array to start appearing.

Enter the multiple-criteria FILTER formula

Type the following formula into the formula bar: =FILTER([Book2]Sheet1!$B:$B, ([Book2]Sheet1!$F:$F="Test")*([Book2]Sheet1!$B:$B<>""), ""). This formula checks if column F contains 'Test' and simultaneously ensures column B is not blank.

Apply the formula

Press the Enter key. The dynamic array will automatically spill the matching results into the adjacent cells below, leaving a blank cell instead of a #CALC! error if no matches are found.

Optimize Formula Performance: Using entire columns (like $B:$B) can slow down performance in large workbooks. It is highly recommended to use specific matching ranges (such as $B$1:$B$1000) instead.

Advanced Spreadsheet Features

Easily Filter and Manage Data with WPS Spreadsheet

WPS Office provides full support for dynamic array functions, including the FILTER function, allowing you to manipulate and extract data across worksheets effortlessly without formula compatibility issues.

1. Open WPS Spreadsheets: Download and launch WPS Office, then open your workbook.
2. Select the target cell: Click on the cell in the worksheet where you want to copy the dynamically filtered data.
3. Input the FILTER formula: Enter your dynamic array formula, ensuring you include the empty string argument at the end, such as =FILTER(Sheet1!B2:B500, (Sheet1!F2:F500="Test"), "").
4. Press Enter to extract data: Press Enter. WPS Spreadsheet will instantly filter the data and update dynamically without displaying #CALC! errors.

Fully compatible with Microsoft Excel formats (.xlsx, .xls) and functions.Supports advanced dynamic array formulas for efficient data analysis.Lightweight software ensuring smooth performance even with large datasets.Free and easy to use with a highly familiar user interface.

microsoft office alternative - wps office

Frequently Asked Questions

What does the #CALC! error mean in the FILTER function?

The #CALC! error occurs when the FILTER function calculates successfully but finds no data that meets your specified criteria. Adding an empty string ("") as the third argument in your formula will display a blank cell instead of the error.

Can I filter data based on more than two conditions?

Yes, you can add more conditions by multiplying additional logical arrays inside the formula's include argument. For example: =FILTER(A:A, (B:B="Yes")*(C:C="No")*(D:D>10), "").

Why is my FILTER formula slowing down my spreadsheet calculation?

Referencing entire columns (e.g., $B:$B) forces the application to calculate over a million rows per condition. To significantly improve performance, limit your references to the specific ranges containing your actual data (e.g., $B$2:$B$1000).

Excel & Data Problems