INDEX/MATCH/SEARCH formula doesn't recognize substrings when searching named range

I have a formula that I found online for searching through a named range (example: test_range) with a cell reference. However I need it to recognize substrings in the search and still find the named range result. I modified the formula for wildcards but it still doesn't work. Using a different match_type also doesn't work.

Here is the formula:
=IF(ISBLANK($D$3),"",IFERROR(INDEX(test_range,MATCH(1,(--(NOT(ISERR(SEARCH("*"&$D$3&"*",test_range))))),0)),"error"))

I've attached an image of the problem and the excel sheet.

Screenshot 2023-08-24 114506.png

The formula for creating the named range is:
=IF(AND(ISBLANK(Test!$A$2:$A$100)),"",LET(test_range,Test!$A$2:INDEX(Test!$A:$A,ROW(Test!$A$2)+5),FILTER(test_range,test_range<>"")))

Thank so much! it works for certain cases (longer sentences) but now it doesn't find a match if its a single word or a shorter sentence. Is there a way to fix this? I would try editing your formula myself but I have no idea how it works.

Attachment 840694

That's true, I guess I would just like it to be pretty close to what JEC. posted but also able to find a result if you type 1 word. Like searching "skill" would return "Master a difficult skill" and searching "I want to master something" would also return "Master a difficult skill". It may not be possible but JEC.'s formula gets pretty close.