Can I use table relationships in formulas?

Question

Are table relationships only for pivot tables & charts, or can they be used in formulas too? The introduction of tables made it easy to write really readable sumifs like this:

=sumifs(counties[population],counties[state],"NY")

When I create another table with a relationship to counties, is there any way to use that in my formulas as well? The following doesn't work. (Imagine there is a region table with state and region columns, and that I've created a relationship between counties and region based on state):

=sumifs(counties[population],region[region],"Northeast")

Edit: What I'm interested to know is if the new table relationships feature added in Excel 2013 has any utility outside of pivot tables and charts.

Answer 1

In Short, No

Unfortunately not with standard tables and pivot tables, you would still need to add a region column to your counties table and VLOOKUP or INDEX\MATCH to bring in the region data from your region table.

Fortunately the VLOOKUP can be quite easy to read as well:

=VLOOKUP([@state],region,2,0)

However, PowerPivot

However, you could always use PowerPivot instead.

Keep your two data tables, and use them as PowerPivot linked tables. Use the Diagram View to specify the relationship join between fields (county in your case).

Then insert a PowerPivot PivotTable like this.