Map External Excel Columns by Header Name
Question details
Import values from external workbooks into destination columns with matching header names.

- 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.
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.
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.
Open the Integrative workbook and Source.xlsx in Excel. Confirm that the source data is on Sheet1, with headers in row 1.
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.
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.
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.

Combine source workbooks by column name with Power Query
Use Power Query for a repeatable import that combines source data even when column positions differ.
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. 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. 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. Fill and verify: Use the cell's fill handle to copy the formula down and across, then compare the results with the source data.

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.




