How to Use INDIRECT to Reference a Specific Row and Column in an Excel Table
Question details
The user needs to use the INDIRECT function to dynamically build a reference to a specific column within the current row of an Excel table.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Constructing a dynamic structured reference to a table column using INDIRECT, specifically targeting the current row's value.
- Observed behavior
- Standard structured references for the current row (like [@ColumnName]) return errors when used inside INDIRECT unless the explicit table name is concatenated into the string.
Ensure your data is formatted as an official Table (via Insert > Table) and identify the exact name of the table and the target column headers you wish to reference dynamically.
Include the Table Name in the INDIRECT Structured Reference
To correctly resolve a dynamic reference to the current row of a table column using INDIRECT, you must prepend the table name to the structured reference string.
When using the INDIRECT function with Excel tables, referencing the current row using just `[@ColumnName]` will result in a #REF! error. The INDIRECT function requires the explicit table name to parse the reference correctly. You must build a string formatted as `TableName[@ColumnName]`.
Check your Table Design tab to confirm the exact name of your table, for example, 'MainData'.
Use a text function to build the column name dynamically. For instance, use `SUBSTITUTE(MainData[[#Headers],[MARKUP YEAR 1]],"MARKUP","PRINCIPAL")` to dynamically change the referenced column.
Wrap your dynamic column name inside the specific structured reference brackets `[@[` and `]]`. Concatenate this with the table name.
Combine everything into the final formula: `=INDIRECT("MainData[@["&SUBSTITUTE(MainData[[#Headers],[MARKUP YEAR 1]],"MARKUP","PRINCIPAL")&"]]")`. Press Enter to calculate the dynamic reference.
Master Dynamic Table References with WPS Spreadsheet
WPS Spreadsheet fully supports advanced array formulas, INDIRECT, and structured table references. You can easily build, edit, and audit complex dynamic formulas using its intuitive interface.
- 1. Open your dataset in WPS Spreadsheet: Launch WPS Spreadsheet and open the workbook containing your data.
- 2. Format data as a Table: Select your data range, go to the Insert tab, and click Table (or use the Ctrl+T shortcut) to create a structured table.
- 3. Enter the INDIRECT formula: Click the cell where you want the result, and type your `=INDIRECT("TableName[@[" & ... & "]]")` formula.
- 4. Evaluate the result: Press Enter to dynamically pull the data from the targeted row and column. Drag the fill handle to apply the formula down the column.
Frequently Asked Questions
Why does INDIRECT("[@ColumnName]") return a #REF! error?
The INDIRECT function cannot implicitly know which table you are referring to when you only provide the current row notation. It requires the explicit table name to resolve the reference. You must use the format "TableName[@ColumnName]" instead.
Can I use INDIRECT to reference table headers?
Yes, you can dynamically reference table headers by constructing a string that explicitly points to the header row. Build the string like "TableName[[#Headers],[ColumnName]]" inside the INDIRECT function.
Does using INDIRECT with tables slow down my workbook?
INDIRECT is a volatile function, meaning it recalculates every time any change is made to the worksheet. If you use it extensively across thousands of rows in a table, it can cause noticeable performance slowdowns.
