Filter function

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) where the classification is matched against a list of classification strings (for example the one starting at K7) and return all variables matching those classification. The list at K7 is dynamic so I can't type individual string names because K7 list is the output of a different filter hence it's dynamic. Also this list is around 1 to 20 rows maximum. Meaning, I can't compare the classification of Metadata with the K7 classification list simply because the array size does not match (as suggested by different post where this could be possible if both arrays have equal size).

Could you please assist what to write in the Include field of Filter function to make it cross check the CLASSIFICATION of Metadata column 4 against the classification at K7?

Note that the propose formula above check one item as an example which is Weather. But again, I want to check against a variable list of strings of classification not fixed one.

thank you.

Excel

djclements
Oct 27, 2023
Riny_van_Eekelen That's quite the formula! 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!

Riny_van_Eekelen

Platinum Contributor

Oct 27, 2023

samery Not the most straight-forward formula, but it I believe it does what you ask for:

=DROP(
    IFERROR(
        REDUCE(
            "",
            K7#,
            LAMBDA(a, v,
                LET(
                    flt, FILTER(
                        Metadata[[Name]:[Description]],
                        Metadata[Classification] =
                            v,
                        ""
                    ),
                    return, VSTACK(
                        a,
                        HSTACK(
                            v,
                            flt
                        )
                    ),
                    return
                )
            )
        ),
        ""
    ),
    1
)

The attached file contains a working example.

samery.xlsx13 KB

djclements
Bronze Contributor
Oct 27, 2023
Riny_van_Eekelen That's quite the formula! 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
  Brass Contributor
  Nov 24, 2023
  Neat! I've been using MMULT to create criteria arrays but this solution is much simpler

peiyezhu
Bronze Contributor
Oct 27, 2023
https://techcommunity.microsoft.com/t5/excel/need-to-search-multiple-words-in-a-cell-and-get-the-output-based/m-p/2490856/page/3
- samery
  Copper Contributor
  Oct 27, 2023
  peiyezhu
  thanks for your response. I think the page you provided works if you know the target strings you are searching for. But in my case they are not known. Hence I won't be able to write individual strings in the index function.

Forum Discussion

Filter function

Resources