英文:
Moving values from one cell to another only if two separate cells match
问题
我正在尝试找到一个公式，只有在两个不同的单元格匹配时才会从单元格中提取值。我已经使用=SUMIFS的公式做到了这一点，通过使用以下公式**=SUMIFS(E7:E24, A7:A24,"Won", D7:D24, D31)**，它会将我需要的数字添加到E7:E24中，检查A7:A24是否包含单词"Won"，然后交叉引用D7:D24和D31，以确保它们匹配以达到我的目标。我之所以需要这个是因为我可以使用下拉菜单而不是手动输入大量重复的信息。我现在正在尝试再次做到这一点，但无法使用=SUMIFS公式，因为我要引入的是文字而不是数字。谷歌在这方面也没有提供太多帮助。我已经创建了一个示例表格在这里，其中提供了一些基本信息。J2包含我的问题，并显示我想要从哪些单元格中提取的内容。对此的任何帮助将不胜感激！
英文:
I am attempting to find a formula that will pull values from a cell only if two separate cells match. I did this already with =sumif by using the formula =SUMIFS(E7:E24, A7:A24,"Won", D7:D24, D31) this would add the numbers I needed in E7:E24, check to make sure A7:A24 contained the word "Won", and then cross reference D7:D24 with D31 to make sure the two matched to give me my goal. I needed this due to being able to use a drop down menu instead of manually inputting lots repetitive info. I am trying to do this again but I am unable to use the =sumifs formula as I am trying to bring in words and not numbers. Google hasn't been much help in this endeavor either. I have created a sample sheet Here with some basic info provided. J2 contains my issue and shows which cells I am trying to pull from. Any assistance on this would be wonderful!
答案1
得分: 0
Here's the translated code portion:
尝试：
=ARRAYFORMULA(TEXTJOIN(", ", 1, IF(REGEXMATCH('C'!G6:AT6, A2), 'C'!G10:AT10, )))
[![0][1]][1]
对于完整数组，您可以使用：
=ARRAYFORMULA(HLOOKUP($A$2, $AD$6:$AT, ROW($A$5:$A$8)+MATCH(K1, $A$10:$A, 0)-1, 0))
[![输入图像描述][2]][2]
Please note that code translations might depend on context and language conventions, so it's a good practice to review and adapt the translated code as needed.
英文:
try:
=ARRAYFORMULA(TEXTJOIN(", ", 1, IF(REGEXMATCH('C'!G6:AT6, A2), 'C'!G10:AT10, )))
for full array you can do:
=ARRAYFORMULA(HLOOKUP($A$2, $AD$6:$AT, ROW($A$5:$A$8)+MATCH(K1, $A$10:$A, 0)-1, 0))
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论