How to sum a multiple-line array of Boolean value into an one-line array?-CodePudding

Dear Excel masters please take a look. Here's the formula that I want to use:

Filter(A1:A4,ISNUMBER(SEARCH({"aa","bb","cc","dd","ee","ff","gg"},B2:B4)))

What ISNUMBER returned is a table of array that Filter function doesn't recognize. Any help?

CodePudding user response：

Assuming the comma represents the separator for horizontal arrays for your version of Excel:

=LET(ζ,{"aa","bb","cc","dd","ee","ff","gg"},FILTER(A1:A4,MMULT(N(ISNUMBER(SEARCH(ζ,B1:B4))),SEQUENCE(COLUMNS(ζ))),""))

CodePudding user response：

You could nest BYROW():

Formula in C1:

=FILTER(A1:A4,BYROW(ISNUMBER(SEARCH({"aa","bb","cc","dd","ee","ff","gg"},B1:B4)),LAMBDA(a,SUM(--a))))

Note that I did expect you to have a typo in the B2:B4 range reference.