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

1
votes

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!

1
arraysgoogle-sheetsgoogle-sheets-formulaarray-formulas
can you share a copy of your sheets? – player0
Working on it now. Just migrating it to another account outside of an organization. – JamesReed68
Updated question with URL to sheets. – JamesReed68

1 Answers

1
votes

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