ARRAYFORMULA, QUERY, and IMPORTRANGE is only displaying results for a single row

user_13255292 0
Viewed 1135

I am wondering if there is an issue with my formula because its not filling the entire column like an array should.

Currently my formula is working perfectly with B2, but its not working for the entire range B2:B.

Here is my formula:

=ARRAYFORMULA(QUERY({
 {IMPORTRANGE("URL TO PRIVATE SHEET","$L2:$O")} 

},"select Col4 WHERE Col1 CONTAINS " & $A2:A))

The IMPORTRANGE sheet looks like... https://docs.google.com/spreadsheets/d/1GFnkuE3Dx-rTuvEV6wj1mCEq3P6cOxbzYco4aFVNw-I/edit?usp=sharing

|   L    |M|N|        O         |
| 000001 |*|*|JohnDoe@email.com |
| 000002 |*|*|JaneDoe@email.com |
| 000003 |*|*|BobDoe@email.com  |

The ARRAYFORMULA is in B2 https://docs.google.com/spreadsheets/d/1JaWUWS3xKOwSX9y7uWUqEU5_Knp8nRgnhlVa1kRNlTo/edit?usp=sharing

|   A    |        B          |
| 000003 |  BobDoe@email.com | <- Contains the formula above and works.
| 000001 |        *          | <- No data: should say "JohnDoe@email.com"
| 000002 |        *          | <- No data: "JaneDoe@email.com"

Is this a limitation in Google Sheets? Thanks!

array-formulas google-sheets-formula google-sheets import arrays
1 Answers

you cant have array in 2nd argument of query like that. try perhaps:

=ARRAYFORMULA(QUERY({{IMPORTRANGE("URL TO PRIVATE SHEET", "L2:O")}},
 "select Col4 
  where Col1 matches '"&TEXTJOIN("|", 1, A2:A)&"'"))

update:

=ARRAYFORMULA(IFNA(VLOOKUP(A2:A, QUERY({
 {IMPORTRANGE("1GFnkuE3Dx-rTuvEV6wj1mCEq3P6cOxbzYco4aFVNw-I", "L2:O")}},
 "select Col1,Col4 
  where Col1 is not null", 0), 2, 0)))

enter image description here

user_56326290
Related