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 ) )
SergeiBaklan's avatar
SergeiBaklan
MVP
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 ) )
emfilipovski's avatar
emfilipovski
Copper Contributor
Sep 24, 2023
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)
))

Resources