Returning value from single row of filtered table, given the column header
 

Hi, I have a table of data by regions (regions are the column headers) that is always filtered to return only one valid row of data. If a region is selected in elsewhere in a cell, I'd like to be able to return the relevant value from the single row of the filtered table. Any suggestions appreciated.. TIA

Returning value from single row of filtered table, given thecolumn header
 

I've found that the SUBTOTAL function is useful because it works with filtered data, ignoring filtered values. So far, it looks like this:

=SUBTOTAL(9, Tbl_AreaCover[Area 1])

I need to replace [Area 1], which is a fixed column of the Tbl_AreaCover table, with a value selected in a cell, so that the formula is dynamic. Any suggestions? TIA.

Returning value from single row of filtered table, given thecolumn header
 

I have replaced Tbl_AreaCover[Area 1] with INDIRECT("Tbl_AreaCover[" & SelectedArea & "]", but find SUBTOTAL inadequate for text values. So I'm still stuck.