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

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!

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.

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