Excel syntax table syntax

Question

I try not to use Excel too much, but when I like it, I like to use structured links because they seem a lot cleaner to write.

If I create a table named "table1" with the columns "col1" and "col2", how would I refer to the first row in "col1" using a structured link in another table? I tried the syntax =table1[[#this row],[col1]] and just get the error message. Is there a syntax like =table1[1,1] or =table1[1,[col1]] ? Of course, this does not work either, but what is equivalent?

This is very annoying as it seems to be simple.

Answer 1

Table1[[#This Row][Column1]] works, but the formula should be on the same line as the row of the table you want to reference.

To refer to the first line elsewhere, use either COUNTIFS(criteria_range1, criteria1 [, criteria_rangen, criterian]) or a 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]) 

Of course, you will need a unique criterion for selecting a row. So for example:

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

SUMIF(Table1[Value], Table1[ID], 2) ... returns the value 4 (or zero if ID = 2 is not found). If your value is not numeric, you cannot use this method, obviously.

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

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

Answer 2

to try

 =INDEX(col1,1) 

you can even access cells in a two-dimensional table using

 =INDEX(reference,row_num,column_num) 

Answer 3

There seems to be no explicit way to use a structured reference to specific rows in a table. As Adrian says, you can use INDEX.

Or you can use an implicit intersection to refer to the same row: if table 1 is on row 5:10, and table 2 is also on row 5:10, then using a structured link with column names will implicitly intersect the same string.

Or you can enter a structured link in the form of a multirow array formula (select several cells, enter the formula and use Ctrl-shift-Enter) on different lines, and it will work.

Answer 4

Instead of INDEX, I would suggest SUMIF. This will allow you to use table values ​​rather than explicit row numbers (which might break if you start filtering or ordering). For example (from the following link), the sum sums the Amount column and includes only those rows where Type equals Check and where Account is Utilities: =SUMIFS(Table1[Amount],Table1[Type],"Check",Table1[Account], "Utilities")

See this link for more details: http://office.microsoft.com/en-us/excel-help/using-structured-references-with-excel-tables-HA010155686.aspx

Answer 5

It would be nice if the table could have a column designated as a primary key (which can be a numeric or string), and then a structured ref can include a way to reference a row using the primary key.

It will be the syntactic sugar around VLOOKUP, but the table can know if it has been sorted by primary key and effective search only in this case. It seems that VLOOKUP is putting evil into it that finds the wrong row if you depend on sorting, especially when tables have a convenient way to sort rows.

Answer 6

In such cases, the trick is to use the Excel OFFSET function:

• Access to the first row of a column named Column1 in the same table: OFFSET([Column1],0,0,1)
• Access to the second row OFFSET([Column1],1,0,1)

and etc.

Of course, you can use this to assign another table and column by simply specifying it with the table name. For example, OFFSET(Table2[Column3],4,0,1) will refer to the fourth row of the column "Column3" of the table "Table2"