Forum Discussion

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

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 ) )

filter table.xlsx16 KB

emfilipovski

Copper Contributor

Sep 23, 2023

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....

SergeiBaklan
MVP
Sep 23, 2023
emfilipovski

Please clarify "111 Apple" is one value in one cell, or that's 111 and Apple in 2 cells.
- emfilipovski
  Copper Contributor
  Sep 23, 2023
  2cells, 111 is in one cell and Apple is in another cell.
  - 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 ) )
    filter table.xlsx16 KB