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
Diamond Contributor
Sep 23, 2023

emfilipovski 

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

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