英文:
Evaluation result changes when using ARRAYFORMULA format
问题
=IF(IFERROR(OR(VLOOKUP($A2, DATA!$A$2:D, 3, FALSE), VLOOKUP($A2, DATA!$A$2:D, 4, FALSE)), FALSE), "SENT", IF(IFERROR(NOT(ISNA(VLOOKUP($A2, REJECTED!$A$2:A, 1, FALSE))), FALSE), "REJECTED", ""))
使用这个公式并向下拖动可以正常工作，所有值都能正确计算。
但是，当我对其进行必要的更改以使用ARRAYFORMULA时，第一个if条件的评估会有不同的行为。
第一行A2的评估从TRUE变为FALSE。
这可能是什么原因呢？
我创建了一个测试表格，复制了我遇到的问题：
https://docs.google.com/spreadsheets/d/1MuBstN9tqVccSExTnI7GOQxfu9OK-nqgyoTHq_pJqYE/edit?usp=sharing
感激任何帮助！
英文:
I'd like to use the following formula with ARRAYFORMULA:
=IF(IFERROR(OR(VLOOKUP($A2, DATA!$A$2:D, 3, FALSE), VLOOKUP($A2, DATA!$A$2:D, 4, FALSE)), FALSE), "SENT", IF(IFERROR(NOT(ISNA(VLOOKUP($A2, REJECTED!$A$2:A, 1, FALSE))), FALSE), "REJECTED", ""))
Using this formula and dragging it down works perfectly fine; all values evaluate correctly.
However, when I make the required changes to use it with ARRAYFORMULA, the first if condition behaves differently.
The evaluation for the first row A2 switches from TRUE to FALSE.
What could be the cause for this?
I have created a test sheet replicating the problem I am having:
https://docs.google.com/spreadsheets/d/1MuBstN9tqVccSExTnI7GOQxfu9OK-nqgyoTHq_pJqYE/edit?usp=sharing
Appreciate any help I can get with this!
答案1
得分: 1
你可以使用BYROW或MAP来扫描整个范围：
=BYROW(A2:A, LAMBDA(each, IF(each="", "", IF(IFERROR(OR(VLOOKUP(each, DATA!$A$2:D, 3, FALSE), VLOOKUP(each, DATA!$A$2:D, 4, FALSE)), FALSE), "SENT", IF(IFERROR(NOT(ISNA(VLOOKUP(each, REJECTED!$A$2:A, 1, FALSE))), FALSE), "REJECTED", "")))))
英文:
You can use BYROW or MAP to scan your whole range:
=BYROW(A2:A,LAMBDA(each,IF(each="","",IF(IFERROR(OR(VLOOKUP(each, DATA!$A$2:D, 3, FALSE), VLOOKUP(each, DATA!$A$2:D, 4, FALSE)), FALSE), "SENT", IF(IFERROR(NOT(ISNA(VLOOKUP(each, REJECTED!$A$2:A, 1, FALSE))), FALSE), "REJECTED", "")))))
答案2
得分: 1
尝试：
=ArrayFormula(IF(IFERROR(IF((VLOOKUP($A2:A, DATA!$A$2:D, 3, FALSE)+ VLOOKUP($A2:A, DATA!$A$2:D, 4, FALSE)=1),"TRUE","FALSE"), FALSE), "SENT", IF(IFERROR(NOT(ISNA(VLOOKUP($A2:A, REJECTED!$A$2:A, 1, FALSE))), FALSE), "REJECTED", "")))
结果：
解释：
您的公式之所以无法工作是因为在arrayformula中使用OR具有不同的行为。您可以用+来替代它，这将返回1或0。然后使用IF条件，如果是1，则返回TRUE，如果是0，则返回FALSE。请参见下面的图像以了解区别：
从：
OR(VLOOKUP($A2:A, DATA!$A$2:D, 3, FALSE), VLOOKUP($A2:A, DATA!$A$2:D, 4, FALSE))
到：
IF((VLOOKUP($A2:A, DATA!$A$2:D, 3, FALSE)+ VLOOKUP($A2:A, DATA!$A$2:D, 4, FALSE)=1),"TRUE","FALSE")
您还可以参考我找到的这个参考视频以获取更多详细信息：
如何在Google表格上使用AND / OR与ARRAYFORMULA？|表格黑客
英文:
Try:
=ArrayFormula(IF(IFERROR(IF((VLOOKUP($A2:A, DATA!$A$2:D, 3, FALSE)+ VLOOKUP($A2:A, DATA!$A$2:D, 4, FALSE)=1),"TRUE","FALSE"), FALSE), "SENT", IF(IFERROR(NOT(ISNA(VLOOKUP($A2:A, REJECTED!$A$2:A, 1, FALSE))), FALSE), "REJECTED", "")))
Result:
Explanation:
The reason why your formula was not working is because using the OR with arrayformula has different behavior. You can replace it with "+", this will return either 1 or 0. Then use IF condition to make it return TRUE if 1, and FALSE if 0. See image below for differences:
From:
OR(VLOOKUP($A2:A, DATA!$A$2:D, 3, FALSE), VLOOKUP($A2:A, DATA!$A$2:D, 4, FALSE))
To:
IF((VLOOKUP($A2:A, DATA!$A$2:D, 3, FALSE)+ VLOOKUP($A2:A, DATA!$A$2:D, 4, FALSE)=1),"TRUE","FALSE")
You may also refer to this reference video I have found for more details:
How to use AND / OR with ARRAYFORMULA on Google Sheets? | Sheet Hacker
答案3
得分: 1
=MAP(A2:INDEX(A2:A,COUNTA(A2:A)),LAMBDA(x,IF(ISERROR(OR(FILTER(DATA!C2:D,DATA!A2:A=x))),
IF(ISNUMBER(XMATCH(x,REJECTED!A2:A))),"REJECTED",""),"SENT")))
英文:
I think you can simplify the formula. Try-
=MAP(A2:INDEX(A2:A,COUNTA(A2:A)),LAMBDA(x,IF(ISERROR(OR(FILTER(DATA!C2:D,DATA!A2:A=x))),
IF(ISNUMBER(XMATCH(x,REJECTED!A2:A)),"REJECTED",""),"SENT")))
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论