Forum Discussion

emfilipovski's avatar
emfilipovski
Copper Contributor
Sep 23, 2023
Solved

Filter function with a range as a criterium.

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 cr...
excel
Formulas and Functions
  • SergeiBaklan's avatar
    SergeiBaklan
    Sep 23, 2023

    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 ) )
OliverScheurich's avatar
OliverScheurich
Gold Contributor
Sep 23, 2023

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.

Resources