Using structured references to link two Excel tables with different starting rows

What are structured references?

A structured reference is a way of using a table name and a column name in a formula instead of a normal cell reference. For example, instead of writing =SUM(C2:C7), you can write =SUM(Table1[Sales Amount]), where Table1 is the name of the table and Sales Amount is the name of the column. This makes the formula easier to read and understand, and also adjusts automatically when you add or remove data from the table.

How to link two Excel tables with different starting rows?

Sometimes, you may have two tables that contain related data, but they start on different rows in the worksheet. For example, you may have a table of sales data that starts on row 5, and another table of product data that starts on row 10. You want to display some columns from the sales table in the product table, based on a common column (such as product ID).

One way to do this is to use the INDEX function with structured references. The INDEX function returns a value from a specific row and column in a range or table. You can use the ROW function to get the current row number, and subtract the row number of the header of the target table to get the relative row number. Then, you can use the column name or index to get the value from the source table.

For example, suppose you have the following two tables:

The first table is named Product and starts on row 10, and the second table is named Sales and starts on row 5. You want to add a column to the product table that shows the total sales amount for each product. To do this, you can use the following formula in cell D11:

=SUMIFS(Sales[Sales Amount],Sales[Product ID],[@[Product ID]])

This formula uses the SUMIFS function to sum the values in the Sales Amount column of the Sales table, where the Product ID column of the Sales table matches the Product ID column of the current row of the Product table. The [@[Product ID]] is a structured reference that refers to the value in the Product ID column of the current row.

If you copy this formula down the column, you will get the following result:

Other approaches

Another way to link two Excel tables with different starting rows is to use the VLOOKUP function. The VLOOKUP function looks up a value in the first column of a table and returns a value from another column in the same row. You can use the VLOOKUP function with structured references to get the values from the source table based on the common column.

For example, if you want to add a column to the product table that shows the sales date of the first sale for each product, you can use the following formula in cell E11:

=VLOOKUP([@[Product ID]],Sales,3,FALSE)

This formula uses the VLOOKUP function to look up the value in the Product ID column of the current row of the Product table in the first column of the Sales table, and return the value from the third column (which is the Sales Date column). The FALSE argument means that the function will only return an exact match. If there is no match, the function will return an error.

If you copy this formula down the column, you will get the following result:

Note that the VLOOKUP function will only return the first match, so if there are multiple sales for the same product, it will not show the latest or the highest sale. To do that, you may need to use other functions such as MAXIFS or LARGE.