Spotlight search has sucked from Day 1 of OSX. You need to use Find Any File [0] or some equivalent tool if you want to search the actual file system without being limited by Spotlight's never-properly-updated "index."

The functionality of Find Any File was built in to Mac OS 9 and earlier. It was rock-solid reliable. So of course Apple removed it and now to get reliable search you have to use a third-party tool. Or 'find' in the Terminal.

[0] https://findanyfile.app/

This is why I rip out spotlight on my mac and replace it with FZF