Function Problems

How to Copy Excel Columns by Matching Headers and Unique IDs

Maira Mehtab Sep 27, 2026 873 views

Question details

The user needs to copy and organize data between multiple worksheets where the column arrangements differ, relying on exact header names and unique row IDs to map the data correctly.

Product: Spreadsheet
Device & OS: not provided
Scenario: Merging or updating datasets across different sheets when columns are out of order, requiring a formula to automatically pull the correct column data based on header text.
Observed behavior: A dynamic formula approach is required to look up headers and retrieve the corresponding column data without manual copy-pasting.

Before you start

Ensure both your source and target worksheets have exact matching text for the headers you wish to copy. Extra spaces or typos will cause the formulas to return errors or blank cells.

Solution 1Recommended

Use INDEX and XMATCH to Pull Columns by Header

Use a combination of INDEX, XMATCH, and LET functions to dynamically find and pull entire columns of data based on matching header names.

This formula searches the source sheet's first row for a matching header name. Once found, it retrieves the entire column's data, eliminating the need to manually match column letters.

Set up your target worksheet

Open your target worksheet where you want the data to appear and ensure your headers are listed in row 1, matching the exact spelling from the source sheet.

Enter the dynamic formula

In cell A2 of the target sheet, enter the following formula: =IF(COUNTIF('Source Sheet'!$A$1:$AB$1,A$1)=0,"",LET(f,INDEX('Source Sheet'!$A$2:$AB$1000,0,XMATCH(A$1,'Source Sheet'!$A$1:$AB$1)),IF(f="","",f)))

Apply the formula

Press Enter to apply the formula. If the header in A1 exists in the source sheet, the entire column of data will be pulled into your target sheet.

Fill across all columns

Select cell A2, click and drag the fill handle (the small green square at the bottom right of the cell) to the right to fill the formula across all your target headers.

Formula Adjustments: Remember to replace 'Source Sheet' with the actual name of your source tab, and adjust the range $A$1:$AB$1000 to match the exact size of your source dataset.

Solution 2

Use Two-Way INDEX and MATCH for Unique Row IDs and Headers

If you also need to match specific rows by a unique ID (like an IC ID) along with matching headers, use a two-way lookup with INDEX and MATCH.

WPS Spreadsheet Solution

Easily Map Complex Data with WPS Spreadsheet

WPS Spreadsheet fully supports advanced dynamic array functions like XMATCH and LET, allowing you to seamlessly organize, match, and extract data across different sheets without manual copying.

1. Open your workbook: Launch WPS Spreadsheet and open the file containing your source and target datasets.
2. Navigate to the target sheet: Click on the sheet tab at the bottom of the screen where you need to organize your copied data.
3. Input the mapping formula: Type the INDEX and XMATCH formula into the first data cell under your header to map the columns.
4. Fill the data: Drag the quick-fill handle across your headers to instantly populate your customized table.

Full compatibility with Microsoft Excel formulas (.xlsx)Native support for advanced functions like XMATCH, LET, and two-way INDEX/MATCHHigh calculation performance for large datasets and complex workbooksFree to use with a familiar, easy-to-navigate interface

microsoft office alternative - wps office

Frequently Asked Questions

Why is my XMATCH or MATCH formula returning an #N/A error?

An #N/A error usually means the header text in your target sheet does not exactly match the header text in the source sheet. Check both sheets for extra leading or trailing spaces, typos, or hidden characters.

Can I use VLOOKUP instead of INDEX and MATCH to match headers?

Standard VLOOKUP searches by a fixed column index number, making it difficult to use when column orders change. To make VLOOKUP dynamic by header, you would need to nest a MATCH function inside it for the column index argument, like this: =VLOOKUP($A2, SourceRange, MATCH(B$1, HeaderRange, 0), FALSE).

What if my unique IDs are not in the first column?

If your unique IDs are not located in the first column of your dataset, VLOOKUP will not work. You should use INDEX and MATCH or the XLOOKUP function, as both can look up data in any direction regardless of the column order.

Excel & Data Problems