I have multiple external drives, some with photo, app and video resources, some with client files and some with backup data dating back to 1988. I also have a number of BluRay backup disks with raw video footage and stock footage. Is there an existing app that can index each drive/disk when I mount it, then store a searchable index on my Mac HD? What I want is to search for, say, 'John Smith logo' and have the app give results for 'Drive G' or 'DISK 21'. I can then mount the indicated drive and retrieve the file, and not have to spend half an hour searching every time. Spotlight, I believe, only stores the index on the drive in question, so cannot search unconnected drives.
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.
With having 30+ TB of external storage I find it absolutely necessary to manage it in some way, and for that I use a paid app named DiskCatalogMaker to keep a database of everything I have on external disks.
It does have a free 30 day evaluation, but it's $38.99 USD for a single user license and $58.99 for a family license. It's bit pricy but after using the eval I bit the bullet and paid the price and wouldn't want to not have it now.
Note: I'm not affiliated with the developer of the product, just a very satisfied user.