SOLVED

# Filter function with a range as a criterium.

Copper Contributor

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

8 Replies

# Re: Filter function with a range as a criterium.

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

# Re: Filter function with a range as a criterium.

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

# Re: Filter function with a range as a criterium.

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

# Re: Filter function with a range as a criterium.

For such sample it could be

Meet:

``````=VSTACK(
FILTER( Table1,
MMULT(--(Table1[Name]=TRANSPOSE(Criteria[Criteria])),
SEQUENCE(ROWS(Criteria[Criteria]),,1,0)
) ) )``````

Don't meet:

``````=VSTACK(
FILTER( Table1,
MMULT(
--(Table1[Name]=TRANSPOSE(Criteria[Criteria])),
SEQUENCE(ROWS(Criteria[Criteria]),,1,0)
) -1 ) )``````

# Re: Filter function with a range as a criterium.

``=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. # Re: Filter function with a range as a criterium.

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

# Re: Filter function with a range as a criterium.

@emfilipovski , you are welcome