Structured references inside a table

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 table name is not necessary in this case since it’s understood to be the current table.

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 do this, I’ll start by entering theVLOOKUP functionnormally.

Notice Excel just uses “pricing” here since the table name alone refers to all data.

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.

We do want a calculated column here, so I need to snag the overwrite option.

Now the pricing in this table is based on a dynamic lookup.