Forum Discussion
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 of schools that each have a unique ID number, and which I've associated together thanks to group numbers (so group 1 is three schools, with a unique ID number for each). So if I want to pull the schools which pertain to group 1 only, I'll filter that array and get those three schools' ID numbers. Here's my formula (which works):
=FILTER(School_IDs,School_groups=1)
I have another (huge) list of all the teachers who work in all of the schools on the territory (each teacher is associated to their school's ID number). What I'm trying to achieve is pulling only the teachers working in the schools of group 1, for example.
So I'd like to filter the huge teacher table using the filtered array of the group 1 schools' ID numbers.
My original idea was something like this (does not work: returns an #N/A error...)
=FILTER(Teacher_table, School_IDs_in_Teacher_table=FILTER(School_IDs,School_groups=1))
I've tried a various number of things, which don't seem to work, the latest being:
=FILTER(Teacher_table,ISNUMBER(XMATCH(FILTER(School_IDs,School_groups=1),School_IDs_in_Teacher_table,0)))
I have not gotten down to figuring out why this does not work, but I have noticed something strange when trying to break down the formula into smaller bits to debug it... When I do :
=XMATCH(FILTER(School_IDs,School_groups=1),School_IDs_in_Teacher_table,0)I notice that I only get 3 row positions in the list, when I should be getting 5 for the first school in the group, and then 5 for the second school, and 2 for the last school (so, a total of 12).
I am thinking (without being sure) that this is due to the fact that the filtered array I feed into the formula is only 3 rows' long, which is a behavior I'd certainly like to avoid... How do I get all the XMATCH positions without being restricted to the size of the original array?
I would be immensely grateful for some help. I understand things would be clearer with the file in question, but I am hesitating posting my workbook here, as those lists are made up of personal data such as names, emails, etc.
Thank you in advance!
--Ari.
Congratulations on getting to a solution!
The problem with your first formula is that you finish up searching the array of teachers against an array of schools as the criterion. Logically, you should be able to achieve this school by school using MAP but unfortunately Microsoft managed to make a disastrous error in deciding that a nested arrays would be an error rather than the natural result of almost every formula!
One way of circumventing the problem is to use REDUCE to run through the filtered list of schools, stacking the result as one goes.
= LET( schools_in_group, FILTER(School_IDs,School_Groups=group), REDUCE("Teachers", schools_in_group, LAMBDA(list,school, VSTACK(list, FILTER(teacher, schools_in_teacher_table=school)) ) ) )(a few errors implementing you names but broadly the same)
The other option is to perform a single FILTER and build the complexity into the criterion, as you have done.
= LET( school_in_group, XLOOKUP(schools_in_teacher_table, School_IDs, School_Groups = group), FILTER(teacher, school_in_group) )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)AND IT WORKS!!!
=FILTER(Teacher_table,ISNUMBER(XMATCH(School_IDs_in_Teacher_table,FILTER(School_IDs,School_groups=1),0)))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) )
