英文:
How do I use ARRAYFORMULA, IF, and VLOOKUP in a formula to populate down the column?
问题
我正在尝试创建一个arrayformula，它将填充到N列中。
我需要它在发布日期和成本与另一个列表（见第二张图片）匹配时找到购买者的名称。
至今，我的公式是：
=ARRAYFORMULA(IF(AND(VLOOKUP(C3:C,'X3'!B2:B,1,0)=C3:C,VLOOKUP(A3:A,'X3'!A2:B,1,0)=A3:A),VLOOKUP(A3:A,'X3'!A2:E,4,0)))
然而这个公式不起作用。我无法弄清楚如何在arrayformula与IF和VLOOKUP一起使用。
英文:
Im trying to create an arrayformula that will populate down the column in COL N.
I need it to find the purchaser name if the posted date and cost match on another list (See the second image).
I know that the costs in the first picture dont show up in the second, but they do further down the list.
So far, my formula is:
=ARRAYFORMULA(IF(AND(VLOOKUP(C3:C,'X3'!B2:B,1,0)=C3:C,VLOOKUP(A3:A,'X3'!A2:B,1,0)=A3:A),VLOOKUP(A3:A,'X3'!A2:E,4,0)))
however this doesn't work. I can't figure out how to use an arrayformula in conjunction with IF and VLOOKUP.
答案1
得分: 2
Sure, here is the translated code:
当你尝试在多个条件之间找到匹配项时，我建议你尝试使用FILTER函数。我不确定你想要检索哪一列，我认为是D列，如果不是，请更改为：
=FILTER('X3'!D2:D,'X3'!B2:B=C3,DATEVALUE('X3'!A2:A)=A3)
要将其用作ARRAYFORMULA，你可以使用MAP函数：
=MAP(A3:A,C3:C,LAMBDA(da,cost,IF(da="",IFNA(FILTER('X3'!D2:D,'X3'!B2:B=da,DATEVALUE('X3'!A2:A)=cost),"")))
如果有用，请告诉我！
英文:
When you're trying to find a match between multiple conditions, I suggest you try with FILTER. I'm not sure what column are you trying to retrieve, I think it's D, if not change it:
=FILTER('X3'!D2:D,'X3'!B2:B=C3,DATEVALUE('X3'!A2:A)=A3)
To use it as ARRAYFORMULA you could use MAP:
=MAP(A3:A,C3:C,LAMBDA(da,cost,IF(da="","",IFNA(FILTER('X3'!D2:D,'X3'!B2:B=da,DATEVALUE('X3'!A2:A)=cost)))))
Let me know if it's useful!
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论