Forum Discussion
emfilipovski
Sep 23, 2023Copper 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 cr...
- Sep 23, 2023
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 ) )
emfilipovski
Sep 23, 2023Copper Contributor
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
Sep 23, 2023MVP
Please clarify "111 Apple" is one value in one cell, or that's 111 and Apple in 2 cells.
- emfilipovskiSep 23, 2023Copper Contributor2cells, 111 is in one cell and Apple is in another cell.
- SergeiBaklanSep 23, 2023MVP
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 ) )
- emfilipovskiSep 24, 2023Copper ContributorThank 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)
))