Function Problems

How to Reference Excel Columns by Header Name on Another Sheet

Maira Mehtab Sep 21, 2026 871 views

Question details

The user needs to extract specific columns of data from a CRM export to another sheet by matching the column header names automatically, avoiding manual column number specification.

Product: Excel
Device & OS: not provided
Scenario: Populating a new sheet with specific columns of CRM data dynamically based on header names.
Observed behavior: The user wants to establish a dynamic reference that looks up header names in row 1 and pulls the corresponding data rows without hardcoding column indexes.

Before you start

Ensure that the header names on your destination sheet exactly match the spelling and spacing of the headers in your source data to avoid formula errors.

Solution 1Recommended

Use CHOOSECOLS and XMATCH to Extract Multiple Columns

This method is highly efficient for modern spreadsheet software, allowing you to return multiple columns dynamically by matching an array of headers all at once.

The CHOOSECOLS function extracts specific columns from a data array, while XMATCH identifies the exact column index numbers by comparing your destination headers with the source headers.

Select the destination cell

Navigate to the first data cell (e.g., H2) directly under your headers on the destination sheet.

Input the dynamic formula

Enter the formula =CHOOSECOLS(A2:E5,XMATCH(H1:J1,A1:E1)) into the cell.

Adjust the data ranges

Modify A2:E5 to match your source data range, H1:J1 to match your destination headers, and A1:E1 to match your source headers. Press Enter to spill the results across the columns.

Dynamic Array Spill: Because CHOOSECOLS is a dynamic array function, you only need to type it in a single cell, and it will automatically populate the data for all matched columns.

Solution 2

Use XLOOKUP to Pull Column Data

XLOOKUP is a versatile function that can search for a single header name and return the entire column of data beneath it.

Advanced Spreadsheets in WPS

Easily Manage and Extract Spreadsheet Data in WPS Spreadsheet

WPS Office provides full support for advanced dynamic array functions like XLOOKUP and MATCH, allowing you to seamlessly reference and extract column data by headers. It is highly efficient, fully compatible with Microsoft formats, and entirely free to use.

1. Open the exported data: Launch WPS Spreadsheet and open your exported CRM dataset.
2. Set up destination headers: Create a new worksheet and type out the desired column headers in the first row.
3. Insert the dynamic formula: Click the cell below your first header, go to the Formulas tab, and insert the XLOOKUP function to map the source data.
4. Save your progress: Save the document in .xlsx format to ensure seamless formula compatibility when sharing.

Fully compatible with Microsoft Excel formulas and .xlsx file formatsNatively supports advanced data lookup functions to handle dynamic referencesLightweight and fast, easily processing large CRM datasets without laggingFree to download and use for everyday office data management

Frequently Asked Questions

Why does my XMATCH or XLOOKUP formula return an #N/A error?

This usually happens if there are trailing spaces or typos in your headers. Ensure the header names on your destination sheet exactly match the source sheet, and check that your lookup arrays are accurately mapped.

How can I pull column data by header name in older spreadsheet versions?

If your software doesn't support XLOOKUP or CHOOSECOLS, you can combine the INDEX and MATCH functions. Use MATCH to locate the column number of the header, and nest it inside INDEX to return the corresponding data array.

Will my destination sheet update automatically if I add new rows to the source?

Yes, provided you format your source data as a Table (using Ctrl+T or the Insert Table option). By utilizing structured references in your formulas, any newly added data rows will instantly reflect on your destination sheet.

Excel & Data Problems