1

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

Improve this question
1
  • 2
    FILTER() function can't compare with many value. Post few sample data and desired output. There may any other formulas to meet your requirement. Commented Jul 18, 2022 at 2:37

2 Answers 2

Reset to default
2

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)

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

Solution for Microsoft 365

Using SCAN, LAMBDA, XLOOKUP, FILTER, Optional: LET

By using SCAN we can use a Lambda Expression against each element of array A. (This will essentially act as a foreach loop)

Therefore, by looking up each value of one array in the other with XLOOKUP inside the LAMBDA passed to SCAN we can return an array equal in length to the one with the lookup values that contains the common values and a bunch of NA for whenever the lookup value from one array is not found in the other.

Since we need to find all the common values between the two arrays, we first need to check which array is largest to use it as our lookup array, to not miss any elements.

Finally, in order to remove all of the unwanted NA values and end up with our clean INTERSECT result we use a FILTER to filter them out.

You can either create a name through the Name Manager (eg. INTERSECT) for the LAMBDA below or just you the body of the LAMDBA wherever you want.

Note: LET is being used for clarity's and sanity's sake but is not necessary.

=LAMBDA(array_a, array_b,
    LET(
        scanResult, IF(
            COUNTA(array_a) > COUNTA(array_b),
            SCAN(
                "",
                array_a,
                LAMBDA(accumulator, current, XLOOKUP(current, array_b, array_b))
            ),
            SCAN(
                "",
                array_b,
                LAMBDA(accumulator, current, XLOOKUP(current, array_a, array_a))
            )
        ),
        FILTER(scanResult, NOT(ISNA(scanResult)))
    )
)
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.