Data Import & Export

Map External Excel Columns by Header Name

Rana GarciaRana Garcia Nov 18, 2026 868 views

Question details

Import values from external workbooks into destination columns with matching header names.

Map External Excel Columns by Header Name
Product
Microsoft Excel
Device & OS
not provided
Scenario
An Integrative workbook needs data from source workbooks whose columns may appear in different positions.
Observed behavior
Source values should appear under the matching destination headers regardless of their column positions.
Before you start

Check that each source workbook has headers in row 1 and that the destination headers use the same names. For the formula method, confirm that corresponding records also appear on the same row in both workbooks.

Solution 1Recommended

Match source columns with INDEX and MATCH

Use a formula when source and destination records align by row but their columns are in different orders.

MATCH finds the source column by its header, and INDEX returns the value from that column on the current row. This method does not match records by an ID or other key.

1
Open both workbooks

Open the Integrative workbook and Source.xlsx in Excel. Confirm that the source data is on Sheet1, with headers in row 1.

2
Enter the formula

In destination cell C2, enter =INDEX('[Source.xlsx]Sheet1'!$A$1:$Z$1000,ROW(),MATCH(C$1,'[Source.xlsx]Sheet1'!$A$1:$Z$1,0)). Replace the workbook name, sheet name, and ranges with your own.

3
Fill the destination range

Drag the fill handle from C2 down and across the destination columns. Each formula uses the header in its own destination column to find the source column.

4
Check the results

Compare a few imported cells with the source workbook. If you see #N/A, check that the destination header exists in the source range and has no extra spaces.

Match source columns with INDEX and MATCH
Keep rows aligned: The example uses the same row number in both workbooks and searches source columns A through Z. Expand the ranges if needed; duplicate source headers match the first occurrence.
Work with external spreadsheet data

Match External Columns by Header in WPS Spreadsheets

Use INDEX and MATCH in WPS Spreadsheets to pull values from a source workbook into columns with matching headers, even when the source columns are reordered.

  1. 1. Open the workbooks: Open the source and Integrative workbooks in WPS Spreadsheets. Check that their headers match and that corresponding records are on the same rows.
  2. 2. Build the lookup formula: In the first destination data cell, enter an INDEX formula with MATCH to locate the destination header in the source header row. Select the source ranges in the open workbook to insert their references.
  3. 3. Fill and verify: Use the cell's fill handle to copy the formula down and across, then compare the results with the source data.
Match source columns by header instead of fixed positionOpen and edit Microsoft Excel XLSX workbooksFill the formula across multiple destination columnsUse familiar spreadsheet formulas and controls
microsoft office alternative - wps office

Frequently Asked Questions

Why does the formula return #N/A?

MATCH returns #N/A when it cannot find the destination header in the specified source header range. Check the header spelling, extra spaces, and the range used in MATCH.

Will the formula still work if source columns move?

Yes, as long as the headers remain within the source ranges and the corresponding records stay on the same rows. MATCH finds each column by header rather than by its original position.

How can I combine files that have columns in different orders?

Import each file through Data > Get Data > From File > From Workbook, prepare consistent headers in Power Query, and use Home > Append Queries. The append operation aligns columns by name.

Can this formula match records that are in a different row order?

No. The example uses ROW() to retrieve the value from the same row number. If records are reordered, use a separate lookup based on a unique record ID as well as the column header.