In this example, the goal is to create a dynamic reference to an Excel Table in a formula. In other words, create a formula that can refer to an Excel table by name as a variable. The easiest way to do this in Excel is to assemble the reference as a text value...Read more
This is a standard INDEX formula. The only trick to the formula is the use of a structured reference to return a range for the table headers:
Table1[#Headers]
This range goes into INDEX for the array argument, with the index value supplied...Read more
This formula is a good example of how structured references can make working with data in Excel much easier. At the core, this is what we're doing:
=AVERAGE(first:last)
where "first" is a...Read more
First, for context, it's important to note that you can use COUNTIFS with a regular structured reference like this:
=COUNTIFS(Table1[Swim],"x")
This is a much simpler formula, but you can't copy it down column H, because the column...Read more
At the core, this formula has a simple pattern like this:
=SUM(first:current)
Where "first" is the first cell in the Total column, and "current" is a reference to a cell in the current row of the Total column.
To get the a...Read more
This formula uses structured references to feed table ranges into the SUMIFS function.
The sum range is provided as Table1[Total], the criteria range is provided as Table1[Item], and criteria comes from...Read more
This is a standard MATCH formula where the lookup values come from column H, the array is the headers in Table1, and match type is zero, to force an exact match.
The only trick to the formula is the use of a structured reference to return a range for the table headers to the MATCH...Read more
At a high level, we use VLOOKUP to extract employee information in 4 columns with ID as the lookup value:
=VLOOKUP($I$4,Table1,MATCH(H5,Table1[#Headers],0),0)
This formula uses structured referencing, a syntax that allows table parts to be referred to by name. When a table is referred to by the name only, Excel returns a reference to the data region of the table only. In this case, the entire table range is...Read more
This formula uses structured referencing, a syntax that allows table parts to be called out by name. When a table is called with the name only, Excel returns a reference to the data region of the table only. In this case, the entire table range is B4:...Read more