Forum Discussion

samery's avatar
samery
Copper Contributor
Oct 27, 2023
Solved

Filter function

hi I have one table called metadata contains 3000+ rows of variables grouped in some classification.   I want to use Filter function to return all variables from Metadata (Name + Description) wher...
excel
  • djclements's avatar
    djclements
    Oct 27, 2023

    Riny_van_Eekelen That's quite the formula! :suprised: I like it, although a simple COUNTIF or ISNUMBER/XMATCH with the parameters reversed will suffice...

     

    =FILTER(MetaData[[NAME]:[DESCRIPTION]], COUNTIF(K7#, MetaData[CLASSIFICATION 1]), "")
// OR:
=FILTER(MetaData[[NAME]:[DESCRIPTION]], ISNUMBER(XMATCH(MetaData[CLASSIFICATION 1], K7#)), "")

     

    Cheers!

     

     

djclements's avatar
djclements
Silver Contributor
Oct 27, 2023

Riny_van_Eekelen That's quite the formula! :suprised: I like it, although a simple COUNTIF or ISNUMBER/XMATCH with the parameters reversed will suffice...

 

=FILTER(MetaData[[NAME]:[DESCRIPTION]], COUNTIF(K7#, MetaData[CLASSIFICATION 1]), "")
// OR:
=FILTER(MetaData[[NAME]:[DESCRIPTION]], ISNUMBER(XMATCH(MetaData[CLASSIFICATION 1], K7#)), "")

 

Cheers!

 

 

DaeyunPablo's avatar
DaeyunPablo
Brass Contributor
Nov 24, 2023
Neat! I've been using MMULT to create criteria arrays but this solution is much simpler

Resources