Pull data from another apreadsheet where Row and Column are strings

Question

I am trying to pull data from a different sheet in google sheets by matching row and column header EG

SheetB

LOC    prox   dist    qty
sjc    12     15     19
dfw    10     16      8
atl
ord 

In sheet a, I am trying to ref dynamically in sheet b something like:

cell g1 = "Prox"
cell a3 = "SJC"

in cell g3 the formula should be the equivalent of "select g3 where col1=a3" and return the value "12"

I haven't found any code that seems to work. I have tried to use query function but it doesn't like dynamic or strings.

Edit: Created an example sheet Summary and Source data https://docs.google.com/spreadsheets/d/1NkCAMrymdPcN29oi_DpHtKbYh7w9tPODzBqU_aCmtnk/edit?usp=sharing

Answer 1

If you really sure you need IMPORTRANGE then it would be like this:

=QUERY(IMPORTRANGE("1NkCAMrymdPcN29oi_DpHtKbYh7w9tPODzBqU_aCmtnk", "Source!E1:K6"),
 "select Col"&MATCH(B$1, 
       IMPORTRANGE("1NkCAMrymdPcN29oi_DpHtKbYh7w9tPODzBqU_aCmtnk", "Source!E1:K1"), 0)&" 
  where Col1='"&$A2&"'", 0)

Otherwise, it could be just: