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

Table1[[#This Row][Column1]] does work, but the formula must be on the same row as the table row you wish to reference.

To reference the first row, elsewhere, use either COUNTIFS(criteria_range1, criteria1 [, criteria_rangen, criterian]) Or the slightly more complex SUMIFS() if you need numeric values instead of counts, as mentioned by studgeek:

SUMIFS(sum_range1, criteria_range1, criteria1 [, criteria_rangen, criterian])

You will of course need a unique row criteria by which to select the row. So, for example:

Table1
ID Value Name
1  2     Two
2  4     Four
3  8     Eight

SUMIF(Table1[Value], Table1[ID], 2) ... returns a value of 4 (or zero if ID=2 not found). If your value is not numeric, then you can't use this method, obviously.

However, akuhn almost hit the real answer, but he didn't go quite far enough in his explanation/example, IMO.

INDEX(Table1[Name], 2) returns "Four" INDEX(Table1, 1, 1) returns 1