Forum Discussion

AriBouaniche's avatar
AriBouaniche
Copper Contributor
Sep 09, 2023

Filter array based on other filtered array as a lookup value

Hi,   I am trying to achieve something I have never done before. I want to filter a huge array, based on the result of another filtered array, in an OR manner. Let me explain...   I have a list o...
Excel on Mac
Formulas and Functions
office 365
AriBouaniche's avatar
AriBouaniche
Copper Contributor
Sep 09, 2023

I have noticed that if I invert the two arguments in the XMATCH function, I get an array that's correctly sized:

=XMATCH(School_IDs_in_Teacher_table,FILTER(School_IDs,School_groups=1),0)
  • AriBouaniche's avatar
    AriBouaniche
    Copper Contributor
    Sep 09, 2023

    AND IT WORKS!!!

    =FILTER(Teacher_table,ISNUMBER(XMATCH(School_IDs_in_Teacher_table,FILTER(School_IDs,School_groups=1),0)))
    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Sep 09, 2023

      AriBouaniche 

      As variant

      =LET(
    isGroup, School_Groups = group,
    schools, FILTER(School_IDs, isGroup),
    isTeacher, MMULT(
        --(schools_in_teacher_table = TRANSPOSE(schools)),
        TOCOL(1 / isGroup, 2) ),
    FILTER( teacher, isTeacher)
)
      • AriBouaniche's avatar
        AriBouaniche
        Copper Contributor
        Sep 11, 2023

        PeterBartholomew1 and SergeiBaklan : thank you both so much for those additional possibilities! Whenever work lets up, I'll investigate those functions I do not know...

         

        You guys have a great day!

         

         

        --Ari.

Resources