Google Sheets – How to Use ARRAYFORMULA with QUERY

formulasgoogle sheets

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?

Best Answer

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.

Related Topic