Function Problems

How to Copy Matching Excel Rows to Another Sheet Using FILTER

Muhammad Talha Sep 25, 2026 870 views

Question details

The user needs to automatically copy specific rows from a master data sheet to a working sheet based on a value selected from a drop-down list, with the results updating dynamically when the selection changes.

How to Copy Matching Excel Rows to Another Sheet Using FILTER

Product: Spreadsheets
Device & OS: not provided
Scenario: Extracting and displaying targeted rows of data dynamically without manual copy-pasting, utilizing a drop-down menu for user selection.
Observed behavior: The user requires a formula-based solution to replace previous results automatically upon selection change, while troubleshooting potential #VALUE! errors.

Before you start

Ensure you are using a spreadsheet application that supports Dynamic Array formulas, such as the FILTER function, and verify that your drop-down list is properly set up using Data Validation.

Solution 1Recommended

Use the FILTER Function with Drop-Down Criteria

The most efficient way to dynamically extract matching rows based on a drop-down selection without using complex VBA macros.

The FILTER function allows you to extract records that meet a specific condition. By pointing the criteria argument directly to a cell containing a drop-down list, the extracted data updates automatically whenever a new item is selected.

Locate the Drop-Down Cell

Identify the cell containing your drop-down list. For example, assume it is located in cell B1 on a sheet named 'Header Sheet'.

Set Up the Target Range

Navigate to your target working sheet (e.g., 'Working_Rates') and select the top-left cell where you want the filtered data to appear, such as A2.

Enter the FILTER Formula

Type the formula: =FILTER(Rates!A1:W912, Rates!A1:A912='Header Sheet'!B1, "- No Matches -") and press Enter. The matched rows will automatically spill into the adjacent cells.

Use the FILTER Function with Drop-Down Criteria

Troubleshooting #VALUE! Errors: If the formula returns a #VALUE! error, verify that the sheet names are spelled correctly, ensure the source range and criteria range have the exact same number of rows (e.g., A1:W912 and A1:A912), and check your software version for dynamic array support.

Efficiently filter and manage data

Easily Filter and Extract Data Dynamically in WPS Spreadsheet

WPS Spreadsheet fully supports modern dynamic array functions like FILTER, allowing you to easily build interactive dashboards and reports. Extract matching rows instantly based on drop-down selections without requiring complex macros.

1. Create a Drop-Down List: Open your workbook in WPS Spreadsheet, go to the Data tab, click 'Validation', and set up a List using your desired criteria options.
2. Prepare Your Target Sheet: Navigate to a blank sheet or specific area where you want the matched rows to be copied dynamically.
3. Apply the FILTER Function: Type =FILTER(SourceData, CriteriaColumn=DropdownCell, "Not Found") into the first cell and press Enter to pull your matching records instantly.

Full support for dynamic array functions including FILTERHighly compatible with Microsoft Excel formulas and .xlsx filesIntuitive Data Validation tools for creating simple drop-down listsLightweight application with exceptionally fast calculation speeds

Frequently Asked Questions

Why does my FILTER formula return a #VALUE! error when using a drop-down list?

A #VALUE! error typically occurs if the dimensions of your arrays do not match. Ensure that the total number of rows in your source data range exactly matches the number of rows in your criteria column range.

Can I use multiple drop-down lists to filter the data?

Yes. You can filter by multiple criteria by multiplying them together in the formula's include argument, such as =FILTER(DataRange, (CriteriaRange1=Dropdown1)*(CriteriaRange2=Dropdown2), "No Matches").

Do I need to use VBA to copy rows based on a selection?

No, VBA is no longer necessary for this specific task. The introduction of dynamic array formulas like FILTER allows you to dynamically extract and update rows using standard formulas.

Excel & Data Problems