Filter function

[samery]

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

[#2]

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

[#3]

https://techcommunity.microsoft.com/t5/excel/need-to-search-multiple-words-in-a-cell-and-get-the-out...

[#4]

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

[#5 · Accepted solution]

@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!

[#6]

Thank you both, excellent contributions. Learned a lot from the nested formulas of Riny although I do enjoy your simplicity Djclements

[#7]

Neat! I've been using MMULT to create criteria arrays but this solution is much simpler