Forum Discussion
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)
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(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.
- 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....
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.
