Explanation
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 using concatenation , then use the INDIRECT function to convert the text reference into a proper Excel reference.
In the example shown, the formulas in L5:L7 behave like these simpler formulas:
=SUM(West[Amount])
=SUM(Central[Amount])
=SUM(East[Amount])
However, instead of hardcoding the table into each SUM formula, the table names are listed in column K, and the formulas in column L use concatenation to assemble a reference to each table. This allows the same formula to be used in L5:L7.
The trick is the INDIRECT function to evaluate the reference. We start with:
=SUM(INDIRECT(K5&"[Amount]"))
which becomes:
=SUM(INDIRECT("West"&"[Amount]"))
and then:
=SUM(INDIRECT("West[Amount]"))
The INDIRECT function then resolves the text string into a proper structured reference :
=SUM(West[Amount])
And the SUM function returns the final result, 27,500 for the West region.
Note: INDIRECT is a v olatile function and can cause performance issues in larger, more complex workbooks.
Explanation
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 function:
Table1[#Headers]
The nice thing about this reference is that it will automatically adjust to any changes in the table. Even when columns are added or removed, the reference will continue to return the correct range.
