Is there an app (like Spotlight) that can index external drives and return search results when the drive is not connected?
I haven't used it in quite some time, but the go-to app for me, way back from System 9 days was CDFinder, now called NeoFinder & still maintained by its author, right up to Catalina.
I used to use it for cataloguing dozens of backup & old project CDs & DVDs. It would do exactly what you require - find a file, tell you what drive it is on, then once you insert that drive, pop open at that location.
Simple yet effective.