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


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

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

  • 1
    share a copy of your sheet with example of desired output
    – user0
    Jul 20, 2019 at 11:59
  • added the sheet in an edit.
    – DwarfCode
    Jul 23, 2019 at 16:11

1 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:


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.