ARRAYFORMULA、QUERY 和 IMPORTRANGE 仅显示单行的结果 [英] ARRAYFORMULA, QUERY, and IMPORTRANGE is only displaying results for a single row
问题描述
我想知道我的公式是否有问题，因为它没有像数组那样填充整列.
目前我的公式与 B2 完美配合，但不适用于整个范围 B2:B.
这是我的公式:
=ARRAYFORMULA(查询({{IMPORTRANGE("URL 到私人表","$L2:$O")}},"选择 Col4 WHERE Col1 CONTAINS"&$A2:A))IMPORTRANGE 表看起来像...
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!
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)))
这篇关于ARRAYFORMULA、QUERY 和 IMPORTRANGE 仅显示单行的结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
