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 criteria ( or I don't know how to make it..) it also shows spill and calc errors, because of the 0's in the table.  I tired other ways with match, isnumber but still can not figure it out. 

 

In this picture is an example of what I am trying to do.  i need to get the = values. All of the table are dynamic and change ..... 

Clarification: 111 and Apple are in separate cells(2 different cells)

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

Resources