Excel structured reference table syntax

Question

I try to avoid using excel too much but when I do I like using structured references as they seem a lot cleaner to write. If I create a table called "table1" with columns "col1" and "col2" how would I reference the first row in "col1" using a structured reference in another table? I have tried the syntax =table1[[#this row],[col1]], and just get an error. Is there a syntax like =table1[1,1] or =table1[1,[col1]]? Of course, this doesn't work either, but what's the equivalent? It's very annoying, as it seems like this should be simple.

Answer 1

try

=INDEX(col1,1)

you can even address cells in a 2-dim table, using

=INDEX(reference,row_num,column_num)

Answer 2

There does not seem to be an explicit way of using structured referencing to particular rows in a table. As Adrian says, you can use INDEX.

Or you can use implicit intersection to reference the same row: if table1 is on row 5:10 and table 2 is also on row 5:10 then using a structured reference with column names will implicitly intersect the same row.

Or you can enter the structured reference as a multirow array formula (select multiple cells, enter the formula and use Ctrl-shift-Enter) in different rows and it will work.