0

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

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

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)

0

Otherwise, it could be just:

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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