20

I have a query that needs to be run for every row in a list. It works nicely when written for a single row:

QUERY(MaraRankData,
  "select J,I,H,G,F,E where 
    A='"&E3&"' and 
    B='"&B3&"' and 
    C="&C3&" and 
    D="&D3&"",
  0
)

That outputs a row of data, as desired. But when I wrap it in ARRAYFORMULA, it still only outputs one row instead of many:

=ARRAYFORMULA(
  QUERY(MaraRankData,
    "select J,I,H,G,F,E where 
      A='"&E3:E&"' and 
      B='"&B3:B&"' and 
      C="&C3:C&" and 
      D="&D3:D&"",
    0)
)

Google Sheets isn't throwing any errors, so I don't know what I'm doing wrong. How can I get ARRAYFORMULA to work with my QUERY so I don't have to repeat the formula on every row?

Improve this question
4
  • To support the comment by red red wine about wrapping a Query() formula with an Arrayformula. It is unnecessary to wrap any formula that already returns/produces an array type result. I.e. don't apply Arrayformula to "Filter", Unique," "Transpose," etc.
    – Xzila
    Aug 8, 2016 at 15:58
  • 1
    Also most of the time you can use a Query, the Filter formula will work. I bet you can solve your problem easier that way.
    – Xzila
    Aug 8, 2016 at 16:05
  • @Xzila If you have a way of doing this with filter, please post an answer: I'd be interested in such a solution.
    – user79865
    Aug 8, 2016 at 16:15
  • 3
    =FILTER({E:E,F:F,G:G,H:H,I:I,J:J},A3:A=E3:E) The first part is just to re-order the columns in the curly brackets. The second is because A to E seems like the only column that mattered as B looks at itself, C looks at itself, etc. Really though a data set would be nice, and perhaps more explanation on the problem. Maybe I just don't understand.
    – Xzila
    Aug 8, 2016 at 17:07

1 Answer 1

Reset to default
17

The arrayformula(query(...)) combination is not supported; there is no concept of a query processing an array of arrays or executing an array of query strings.

You have two options: (a) repeat query on every row; (b) use vlookup to retrieve columns of data, as explained below. For example: 

=arrayformula(vlookup(E3:E, {A3:A, J3:J}, 2, false))

takes one element of E3:E at a time, finds this element in column A, and retrieves the corresponding element of column J. With this approach you would need six separate vlookups to get the columns J,I,H,G,F,E, but you won't need a separate command for each row.

A complication is that vlookup accepts only one search key, and you want to search by 4 parameters, 

A='"&E3&"' and 
B='"&B3&"' and 
C="&C3&" and 
D="&D3&

This can be worked around by concatenating these into one search key: you can have a column like Z, 

=arrayformula(E3:E & "|" & B3:B & "|" & C3:C & "|" & D3:D)

which concatenates four search parameters into one pipe-delimited search key. Do the same for the table to be searched, and then compare these keys using vlookup.

Improve this answer
1

Your Answer

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

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