Filter function with a range as a criterium.

[emfilipovski]

Hi,

I need help with Excel, trying to filter out a range with values and text, by another range criterium with text.  I am using a filter function but it can only use one criteria, not a range of criteria ( or I don't know how to make it..) it also shows spill and calc errors, because of the 0's in the table.  I tired other ways with match, isnumber but still can not figure it out. 

In this picture is an example of what I am trying to do.  i need to get the = values. All of the table are dynamic and change ..... 

Clarification: 111 and Apple are in separate cells(2 different cells)

[#2]

And I also need the other values in a separate formula, that are not in the Criteria list. The remaining values are Apple, Orange, KKK....

[#3]

@emfilipovski 

Please clarify "111 Apple" is one value in one cell, or that's 111 and Apple in 2 cells.

[#4]

2cells, 111 is in one cell and Apple is in another cell.

[#5 · Accepted solution]

@emfilipovski 

For such sample

it could be

Meet:

=VSTACK(
    Table1[#Headers],
    FILTER( Table1,
        MMULT(--(Table1[Name]=TRANSPOSE(Criteria[Criteria])),
              SEQUENCE(ROWS(Criteria[Criteria]),,1,0)
        ) ) )

Don't meet:

=VSTACK(
    Table1[#Headers],
    FILTER( Table1,
        MMULT(
            --(Table1[Name]=TRANSPOSE(Criteria[Criteria])),
            SEQUENCE(ROWS(Criteria[Criteria]),,1,0)
        ) -1 ) )

[#6]

@emfilipovski 

=FILTER(Tabelle1,BYROW(Tabelle1[values],LAMBDA(x,NOT(OR(ISNUMBER(SEARCH(x,Tabelle2[Criteria])))))))

An alternative could be FILTER with LAMBDA. The table names are Tabelle1 (Table with values) and Tabelle2 (Criteria) in this example.

=DROP(REDUCE("",Tabelle2[Criteria],LAMBDA(x,y,VSTACK(x,FILTER(Tabelle1,ISNUMBER(SEARCH(y,Tabelle1[values])))))),1)

This is the formula for the results that match the criteria. This formula is in cell G2.

[#7]

https://techcommunity.microsoft.com/t5/excel/need-to-search-multiple-words-in-a-cell-and-get-the-out...

[#8]

Thank you, this seems to work. Hopefull no errors can occur because it is intended to be dynamic and change.... this is the formula I used =FILTER($AI$6:$AJ$601;
MMULT(--($AJ$6:$AJ$601=TRANSPOSE($AK$4:$AK$599));
SEQUENCE(ROWS($AK$4:$AK$599);;1;0)
))

[#9]

@emfilipovski , you are welcome