英文:
How would you filter out a dynamic array using a dynamic array (Non exact match)?
问题
这是昨天我发布的问题的后续。我在Excel中有两个动态数组，如图所示。一个是C1#，用于搜索的标准，另一个是F1#，我想要搜索的动态数组。我想要得到如"Wanted Output"所示的动态数组输出。我能够得到这个输出，但我不确定如何让它输出"None"，包括单词"One"，我目前只能让它输出完全匹配。有没有办法在不需要完全匹配的情况下做到这一点？谢谢。
英文:
This is a follow up to the question I posted yesterday. I have two dynamic arrays in excel, as shown in the image. One is C1#, which is the criteria for the search, and one is F1#, which is the dynamic array I want to search in. I want to get the dynamic array output as shown in the Wanted Output. I am able to get this output, however I am not sure how to get it to output "None", which includes the word "One", I am currently only able to get it to output exact matches. Is there any way to do this without requiring exact matches? Thank you.
答案1
得分: 3
尝试使用 MMULT( )
• 在单元格 I1 中使用的公式
=LET(
a,TOROW("&"*C1#&"*"),
b,SEQUENCE(ROWS(C1#),,,0),
IF(MMULT(N(ISNUMBER(SEARCH(a,F1#))),b),F1#,""))
英文:
Try using <kbd>MMULT( )</kbd>
• Formula used in cell I1
=LET(
a,TOROW("*"&C1#&"*"),
b,SEQUENCE(ROWS(C1#),,,0),
IF(MMULT(N(ISNUMBER(SEARCH(a,F1#))),b),F1#,""))
答案2
得分: 2
Formula in G1:
=REPT(F1:F9,BYROW(ISERR(SEARCH(TEXTSPLIT(A2,";"),F1:F9)),LAMBDA(r,1-AND(r))))
英文:
ALternatively:
Formula in G1:
=REPT(F1:F9,BYROW(ISERR(SEARCH(TEXTSPLIT(A2,","),F1:F9)),LAMBDA(r,1-AND(r))))
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论