Function Problems

Reference an Excel Table Column by Relative Position

Amos Gikunda Nov 12, 2026 868 views

Question details

The user needs to reference an Excel table column without relying on its worksheet column number or physical location.

Product: Microsoft Excel
Device & OS: not provided
Scenario: A table column must be returned directly or selected by its relative position within the table based on its header.
Observed behavior: COLUMN(Table1[data]) returns the worksheet column number rather than the column's relative position within the table.

Before you start

Confirm the table name in Table Design and check the exact spelling of the target header in the table's header row. In the examples below, the table is named Table1 and the target header is Data.

Solution 1Recommended

Return the Excel table column with a structured reference

Use this method when you know the table and column header and want to return the entire column directly.

A structured reference points to the table column by its table name and header, so it remains independent of the column's worksheet location.

Select the result cell

Open the worksheet and click the cell where you want the table column values to appear.

Enter the structured reference

Type =Table1[data] in the formula bar, replacing Table1 and data with your actual table name and column header.

Confirm the formula

Press Enter to return the referenced table column. If your version supports spilled arrays, the values will populate the cells below automatically.

Use the table name and header: The structured reference follows the table column even if the table is moved or its worksheet position changes.

Solution 2

Select an Excel table column by its relative table position

Use this method when the column must be found by its header and selected according to its position within the table.

Reference Table Columns by Header or Relative Position in WPS Spreadsheet

WPS Spreadsheet lets you work with table-based formulas and organize references without depending on fixed worksheet column numbers. Use a structured reference for a known column or combine MATCH with CHOOSECOLS when selecting a column by its position within the table.

1. Open the spreadsheet in WPS: Launch WPS Office, choose Spreadsheet, and open the workbook containing the table.
2. Check the table name and header: Click inside the table and open the Table Design tab to confirm the table name and target column header.
3. Enter a direct column reference: Select an output cell and enter =Table1[data], replacing the sample table name and header with your own.
4. Use relative-position selection when needed: For a header-based relative selection, enter =CHOOSECOLS(Table1,MATCH("Data",Table1[#Headers],0)) and replace Data with the required header.

Reference table columns by their headers instead of fixed worksheet locationsUse MATCH and CHOOSECOLS to select a column by its relative table positionWork in a familiar spreadsheet interface with lightweight performanceOpen and edit Microsoft Excel XLSX files with compatible formatting and formulas

microsoft office alternative - wps office

Frequently Asked Questions

How do I reference an entire Excel table column by name?

Enter =Table1[data] in a cell, replacing Table1 with the table name and data with the exact column header. This uses a structured reference and does not depend on the worksheet column letter.

How can I select a table column by its position within the table?

Use =CHOOSECOLS(Table1,MATCH("Data",Table1[#Headers],0)). MATCH returns the relative position of Data in the table header row, and CHOOSECOLS returns that column.

Why does COLUMN(Table1[data]) return the wrong number?

COLUMN returns the column's worksheet position, such as column 5 on the sheet. It does not return the column's relative position within the Excel table.

What should I do if CHOOSECOLS is not recognized?

Your Excel or spreadsheet version may not support CHOOSECOLS. Use the direct structured reference =Table1[data], or use an INDEX-based formula that is supported by your version.

Excel & Data Problems