Help using FILTER to return multiple results from XLOOKUP

[cmstoren]

Hey all - apologies if this is really simple but even after watching multiple videos I can't seem to get the answer I need.
I'm using XLOOKUP with wildcards to find all the students taking a particular subject. I think I have the XLOOKUP part right but I'm struggling to get the FILTER function to work for me (this was recommended on a video). My formula is: =XLOOKUP("*"&$A1&"*",Subject_Lookup!D:D,Subject_Lookup!$A:$A,0,2) and this is pulling up the first student in the list but not all.

If anyone could help that would be great.

Thanks,

Chloe

 

[hagia_sofia]

Hello, please try this:

=FILTER(Subject_Lookup!$A:$A,ISNUMBER(SEARCH(A1,Subject_Lookup!D:D)))

 

[cmstoren]

Hello, please try this:

=FILTER(Subject_Lookup!$A:$A,ISNUMBER(SEARCH(A1,Subject_Lookup!D:D)))

Hey - thanks for the response. This is giving me a CALC# error which I'm not too sure on.

 

[hagia_sofia]

Thanks for the feedback - maybe the current value in A1 is not in the column D? The amended formula will return 0 is it is the case:

=FILTER(Subject_Lookup!$A:$A,ISNUMBER(SEARCH(A1,Subject_Lookup!D:D)),0)

 

[cmstoren]

Perfect - thank you so much for your help!