Excel filter using the Dynamic Array of another filter function-CodePudding

I have been struggling with this for a while and am unable to find a solution on google, so any help would be greatly appreciated!

The function is trying to list all the courses that a student is taking, based on all the students that are taking "English".

I am trying to filter a table based on the output of another filter function. This function below works perfectly when the output of the second filter function is only 1 value, but as soon as it outputs a dynamic array of more than 1 value, an error occurs. Please let me know if there is an easier way to do this, or if I am doing something wrong.

Thank you.

=FILTER(Table1[[#All],[Course]],Table1[[#All],[Surname - Given Names]] = FILTER(Table1[[#All],[Surname - Given Names]],Table1[[#All],[Course]]="English"))

CodePudding user response：

As Harun24hr said, you cannot compare each individual element (given_names) in the dynamic array with multiple values. The filter function simply takes one final TRUE or FALSE for each row in the table while your written criteria give a list of TRUE or FALSE for each row which is why your external filter fails when you have multiple results of your inner filter.

You can use this formula instead:

=LET( table,             Table1[[#All],[Course]], 
      criteria_clmn,     Table1[[#All],[Surname - Given Names]], 
      filter_list,       FILTER(Table1[[#All],[Surname - Given Names]],Table1[[#All],[Course]]="English"),   
      criteria_result,   XLOOKUP(criteria_clmn, filter_list, filter_list = filter_list, FALSE),   
      filtered_table,    FILTER(table,  criteria_result,  NA()),
      filtered_table
    )

you can change/play with the first three parameters (table, criteria_clmn, and filter list)