Function Problems

How to Automatically Copy a Matching Row to Another Table in Excel

Maira Mehtab Sep 22, 2026 869 views

Question details

The user needs a method to dynamically extract and copy an entire row of data from a source table into a destination table based on a value selected in a secondary table.

Product: Spreadsheets
Device & OS: not provided
Scenario: Managing related data across three separate tables and needing an automated workflow where selecting an item in Table B instantly populates the corresponding row from Table A into Table C.
Observed behavior: Currently, transferring the matched row data requires manual copying and pasting, which is inefficient and prone to errors. The goal is to automate the data retrieval into the target table.

Before you start

Ensure your source data is formatted as an official Excel Table (using Ctrl+T) so that any formulas you write will automatically adapt when new rows are added.

Solution 1Recommended

Use the FILTER Function to Dynamically Extract Rows

The FILTER function is the most efficient and modern way to automatically pull entire rows of data that match your specified criteria into a new destination.

Dynamic array functions allow a single formula to spill results across multiple columns automatically. This means you only need to write the formula in the first cell of your destination table.

Select the destination cell

Click on the top-left cell of your destination table (Table C) where you want the copied row to appear.

Enter the FILTER formula

Type the formula =FILTER(TableA, TableA[LookupColumn] = SelectionCell, "No match"). Replace 'TableA' with your source data range, and 'SelectionCell' with the cell in Table B that contains your dropdown choice.

Execute the formula

Press Enter. The function will dynamically spill the entire matching row across the adjacent columns.

Dynamic Updates: Whenever you change the selection in Table B, the row displayed in Table C will update instantly.

Solution 2

Use VLOOKUP for Standard Data Lookup

If you are using a spreadsheet version that does not support dynamic arrays, you can use VLOOKUP combined with the COLUMN function to pull row data.

Automate Table Data Extraction Easily with WPS Spreadsheet

WPS Spreadsheet provides comprehensive support for both classic lookup formulas and modern dynamic array functions. You can easily automate data transfers between tables without needing complex VBA programming or external tools.

1. Open your workbook: Launch WPS Spreadsheet and open the document containing your three tables.
2. Access the Formula tool: Select the target cell in Table C and navigate to the 'Formulas' tab on the top ribbon.
3. Insert the required function: Click 'Insert Function', search for 'FILTER' or 'VLOOKUP', and follow the prompts to link your source table to your criteria cell.

Fully compatible with Microsoft Excel formulas and table formats (.xlsx)Supports modern functions like FILTER and XLOOKUP for instant row extractionBuilt-in Formula Wizard helps beginners build complex queries step-by-stepFree to use with a lightweight and familiar tabbed interface

microsoft office alternative - wps office

Frequently Asked Questions

Why does my FILTER formula return a #CALC! error?

A #CALC! error typically occurs when the FILTER function finds no rows matching your criteria. You can fix this by adding a fallback value in the third argument, for example: =FILTER(A2:D10, A2:A10="Value", "No results").

Can I automatically copy rows based on multiple conditions?

Yes. When using the FILTER function, you can multiply conditions together to require both to be true. For example: =FILTER(TableA, (TableA[Col1]="Target1") * (TableA[Col2]="Target2")).

How do I prevent zero values appearing in blank cells?

If your source row contains empty cells, lookup formulas might return a '0'. You can prevent this by appending an empty string to your formula like this: =VLOOKUP(...) & "".

Will copying rows this way slow down my workbook?

If you use a few dynamic array functions (like FILTER), performance remains fast. However, using thousands of individual VLOOKUP formulas across a massive dataset can reduce calculation speed. In such cases, using the Advanced Filter tool or a simple macro is recommended.

Excel & Data Problems