SOLVED

Filter function with a range as a criterium.

Copper Contributor

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 ..... Excel.problem.JPG

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

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

@emfilipovski 

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

2cells, 111 is in one cell and Apple is in another cell.
best response confirmed by emfilipovski (Copper Contributor)
Solution

@emfilipovski 

For such sample

image.png

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 

=FILTER(Tabelle1,BYROW(Tabelle1[values],LAMBDA(x,NOT(OR(ISNUMBER(SEARCH(x,Tabelle2[Criteria])))))))

An alternative could be FILTER with LAMBDA. The table names are Tabelle1 (Table with values) and Tabelle2 (Criteria) in this example.

=DROP(REDUCE("",Tabelle2[Criteria],LAMBDA(x,y,VSTACK(x,FILTER(Tabelle1,ISNUMBER(SEARCH(y,Tabelle1[values])))))),1)

This is the formula for the results that match the criteria. This formula is in cell G2.

filter.png

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

@emfilipovski , you are welcome

1 best response

Accepted Solutions
best response confirmed by emfilipovski (Copper Contributor)
Solution

@emfilipovski 

For such sample

image.png

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

View solution in original post