How To Reference an Excel Table Cell by Row Number or Horizontal Header Using Table Notation?

Question

I'd like to reference a single cell in a table, from outside the table, using square-bracket sheet-formula notation.

Something like: [MyTable[MyField] 3] or [MyTable[MyField] 3:3]

-to reference the 3rd row of the MyField column, or:

[MyTable[MyField] MyRow]

-to reference the MyRow row (leftmost row-header) of the MyField column.

Needs to work from outside the table, ie can't use @ or #ThisRow.

Not looking for methods involving MATCH, INDEX, OFFSET, etc. Not looking for VBA methods. Just straightforward table-notation. Not looking for manually creating named ranges.

Why? Because, Tables :)

Pre-2013 Excel.

(PS, didn't there used to be a way (pre-Tables) to reference cells by row and column headers? I think it was maybe called "auto-naming", or something like that.)

Answer 1

You can also use index() function as in:

index(MyTable[MyField], 3)

So you get row 3 from the column MyField in table MyTable.

Reference: https://www.ozgrid.com/forum/forum/help-forums/excel-general/116365-reference-a-single-cell-in-a-table-using-structured-referencing

Answer 2

We can reuse the idea of the intersection operator (i.e. a space between two references) and improve it to have the relative row number of the targeted item in the table, referred to as row_nb here:

=tbl[col] OFFSET(tbl[[#Headers],[col]],row_nb,)

or just without intersection actually (cf. comment below):

=OFFSET(tbl[[#Headers],[col]],row_nb,)

E.g. =Table1[Column2] OFFSET(Table1[[#Headers],[Column2]],2,)

This way you do not depend on the position of the table in the worksheet. Well, it yields a more complicated formula where table name tbl and column name col appear twice. Here are some comments about it:

1. You can of course keep the OFFSET(...) part only. The downside is that the formula will never return any error if row_nb exceeds the actual number of line items in the table. It'll return e.g. just 0 if the cells below the table are empty.

2. Keeping a formula that throws an error when we refer to an off-table row, we can further improve it: make it "dynamic" by letting tbl, col and row_nb be parameters:

=INDIRECT(tbl&"["&col&"]") OFFSET(INDIRECT(tbl&"[[#Headers],["&col&"]]"),row_nb,)

Assuming that we've defined tbl, col and row_nb as named ranges. Or else just use cell addresses:

=INDIRECT(A1&"["&A2&"]") OFFSET(INDIRECT(A1&"[[#Headers],["&A2&"]]"),A3,)

I'm not a big fan of INDIRECT but in this case it really comes in handy to let the formula adapt to various tables, columns and line items.

Answer 3

heh, well this works:

=Table1[Column2] 3:3

So that's progress :)

Just awesome would be a way to reference a row by the contents of left-most column.

Thx!