Inside a table, structured references work a lot like structured references outside a table, except the table name is not used when it’s implied. To illustrate, let’s look at some examples. The formula to calculate the Total in this table is just Quantity times Price. Notice when I point and click to enter the formula, Excel automatically builds the structured reference. The syntax here means to multiply the value in the current row of the Quantity column by the value in the current row of the Price column. The table name is not necessary in this case since it’s understood to be the current table. If I enter a formula to calculate the Tax, assuming a tax rate of 7.5%, we again see the @ symbol to indicate current row. What if we need to reference another table from inside the current table? In that case, we’ll need to add the table name. To illustrate with an example, I’ll replace the hardcoded prices in the Price column with a VLOOKUP formula that retrieves a price from the pricing table to the right. To do this, I’ll start by entering the VLOOKUP function normally. For lookup_value, we want to use the Color from the current row, and for table_array, we want all of the data in the pricing table. Notice Excel just uses “pricing” here since the table name alone refers to all data. If I was to point to other parts of the table, we’d see a more complete structured reference. But the table data is all we need here. For column_index, we want 2. And we want to use zero or false as the last argument to force an exact match. In this case, since we’re replacing a static value with a formula, Excel asks for confirmation before overwriting all values. We do want a calculated column here, so I need to select the overwrite option. Now the pricing in this table is based on a dynamic lookup. If I change the price of say, the color Orange, we’ll see that change ripple through the original table.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
