Function Problems

How to Copy Data Between Excel Sheets by Matching Headers and Names

Guest Writer Sep 28, 2026 869 views

Question details

The user needs a method to dynamically copy selected data from a MAIN sheet to a fixed-order LINKED sheet by matching column headers and row names, while appending new entries at the bottom.

How to Copy Data Between Excel Sheets by Matching Headers and Names

Product: Excel
Device & OS: not provided
Scenario: Managing a master dataset that feeds into a specifically ordered linked sheet, which is directly connected to a PowerPoint presentation.
Observed behavior: The user requires a formula, VBA, or Power Query solution to map data dynamically via headers and identifiers without breaking existing PowerPoint slide links when new parts are added.

Before you start

Ensure that your column headers in both the MAIN and LINKED sheets match exactly, without any extra trailing spaces or spelling differences.

Solution 1Recommended

Use INDEX and MATCH for a Two-Way Lookup

This formula method is highly recommended for dynamically intersecting column headers and row names without altering the fixed layout required for your PowerPoint links.

A two-way lookup uses the MATCH function twice inside an INDEX function: once to find the correct row (the name), and once to find the correct column (the header).

Set up the LINKED sheet structure

Ensure Row 1 of your LINKED sheet contains the exact headers from your MAIN sheet, and Column A contains the common/English names.

Enter the formula

In cell B2 of the LINKED sheet, enter the following formula: =INDEX(MAIN!$A$1:$Z$1000, MATCH($A2, MAIN!$A$1:$A$1000, 0), MATCH(B$1, MAIN!$1:$1, 0))

Apply across the data range

Drag the fill handle to copy this formula across all the required columns and down to the existing rows in your LINKED sheet.

Append new parts at the bottom

When new names are added to the MAIN sheet, manually type or paste those new names at the bottom of Column A in the LINKED sheet, then drag the formulas down to fetch their data.

Use INDEX and MATCH for a Two-Way Lookup

Maintaining Links: By adding new names only at the bottom, your existing cell references in PowerPoint will not shift, keeping your presentations intact.

Solution 2

Use Power Query to Merge Data

Power Query is an excellent alternative if you want Excel to automatically pull matching columns from the MAIN sheet and merge them with a master list of names.

Advanced Spreadsheet Data Management

Seamlessly Map and Link Spreadsheet Data with WPS Office

WPS Spreadsheet provides powerful two-way lookup functions and seamless compatibility with Microsoft Excel, making it incredibly easy to link data across sheets and presentations.

1. Open your workbook: Launch WPS Spreadsheet and open your master dataset.
2. Apply lookup formulas: Use the exact same INDEX and MATCH combinations to dynamically link your header and name intersections.
3. Link to presentation: Highlight the organized data, copy it, and use 'Paste Special > Paste Link' in WPS Presentation to keep your slides automatically updated.

100% compatible with Microsoft Excel (.xlsx) formats and PowerPoint data linksBuilt-in support for advanced functions like INDEX, MATCH, and XLOOKUPFree, lightweight, and features a familiar user interface for seamless migrationEfficiently handles large datasets without lagging

microsoft office alternative - wps office

Frequently Asked Questions

Why does my INDEX MATCH formula return an #N/A error?

The #N/A error typically occurs because the lookup value does not exactly match the data array. Check for trailing spaces, hidden characters, or minor spelling differences in your column headers or row names between the MAIN and LINKED sheets.

Can I use XLOOKUP to match both headers and names?

Yes, you can nest XLOOKUP functions to perform a two-way lookup. Use the outer XLOOKUP to search down the rows for the name, and use an inner XLOOKUP for the return array to search across the columns for the header.

Will sorting the MAIN sheet break the PowerPoint links?

No, sorting the MAIN sheet will not break the links if you are using INDEX and MATCH on the LINKED sheet. The formula will automatically find the new row location of the data. However, do not sort the LINKED sheet itself, as the PowerPoint links are tied to specific cell references in that sheet.

Excel & Data Problems