英文:
Excel Filter Function Include Different Array for Filtering
问题
I wanted to make a filter function, but the array for criteria is referring to a different cell
这里是一个示例
我的数组是 C3:C17,我想根据 A3:A5 列表上的名字来过滤这个数组
这里我尝试使用这个公式
=FILTER(HSTACK($C$3:$D$17),C3:C17=A3:A17)
我希望返回 Agent 1、5 和 10 以及它们相应的 Leaders 列在 F3 单元格上,但是公式只返回了一个结果,即 Agent 1 和 Leader 1
期望/所需结果
英文:
I wanted to make a filter function, but the array for criteria is referring to a different cell
Here is an example
My array is C3:C17, I wanted to filter the names on this array based on the list from A3:A5
Here I tried using this formula
=FILTER(HSTACK($C$3:$D$17),C3:C17=A3:A17)
I am expecting to return Agent 1, 5 and 10 with their corresponding Leaders on cell F3 but instead the formula only return 1 result which is Agent 1 and Leader 1
Expecting/Desired result
答案1
得分: 3
使用 ISNUMBER 和 XMATCH:
=FILTER(C3:D17,ISNUMBER(XMATCH(C3:C17,A3:A5)))
答案2
得分: 3
另一个选择: BigBen 先生很快,否则我可能会选择使用 ISNUMBER() 和 XMATCH()
• 在单元格 F3 中使用的公式
=FILTER(C3:D17,MMULT(N(C3:C17=TOROW(A3:A5)),SEQUENCE(ROWS(A3:A5))))
为了避免在名单中进行额外更改,这将使用 TOROW() 和 DROP() 函数: -> 第一个用于排除空白,而后者用于排除前面的 2 行。
• 在单元格 F3 中使用的公式
=LET(α,TOROW(DROP(A:A,2),1),
FILTER(C3:D17,MMULT(N(α=C3:C17),SEQUENCE(COLUMNS(α)))))
英文:
Another alternative: BigBen Sir was pretty quick otherwise I would have opted for ISNUMBER() with XMATCH()
• Formula used in cell F3
=FILTER(C3:D17,MMULT(N(C3:C17=TOROW(A3:A5)),SEQUENCE(ROWS(A3:A5))))
To avoid additional changes in the roster, this will work using TOROW() & DROP() function: -> The first one is used to exclude the blanks while the latter is used to exclude the top 2 rows.
• Formula used in cell F3
=LET(α,TOROW(DROP(A:A,2),1),
FILTER(C3:D17,MMULT(N(α=C3:C17),SEQUENCE(COLUMNS(α)))))
答案3
得分: 2
使用 INDEX/XMATCH 进行筛选
- 请注意,此简化仅在您希望返回与“查找”列 (
A) 中的值数量相同的结果时有效,即如果列C中没有重复值。否则,您需要使用FILTER并切换XMATCH参数,类似于 BigBen 在他的答案中所做的操作。 - 请注意,您不需要变量
l、s、dr和dc,您可以将所有内容写成一行。它们仅用于更好地理解正在发生的情况,即使使其更可读。
=LET(lrg,A3:A17,srg,C3:D17,sc,1,
l,TOCOL(lrg,3),s,INDEX(srg,,sc),
dr,XMATCH(l,s),dc,SEQUENCE(,COLUMNS(srg)),
IFERROR(INDEX(srg,dr,dc),""))
英文:
Filter Using INDEX/XMATCH
- Note that this simplification only works if you want to return as many results as there are values in the 'lookup' column (
A) i.e. if there are no duplicates in columnC. Otherwise, you need to useFILTERand switch theXMATCHparameters i.e. do something like BigBen did in his answer. - Note that you don't need the variables
l,s,dr, anddci.e. you can write all of it in one long line. They are used just to better understand what's going on i.e. to make it more readable.
=LET(lrg,A3:A17,srg,C3:D17,sc,1,
l,TOCOL(lrg,3),s,INDEX(srg,,sc),
dr,XMATCH(l,s),dc,SEQUENCE(,COLUMNS(srg)),
IFERROR(INDEX(srg,dr,dc),""))
答案4
得分: 2
另一种替代方法:
在F3中的公式:
=FILTER(C3:D17,COUNTIF(A3:A5,C3:C17))
或者简单点(但速度较慢):
=FILTER(C:D,COUNTIF(A3:A5,C:C))
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。





评论