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 ) )
emfilipovski's avatar
emfilipovski
Copper Contributor
Sep 23, 2023
2cells, 111 is in one cell and Apple is in another cell.
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
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