Lookup value in another "random" workbook

[wadergirl]

So, I'm not even sure if this is possible.

I would like a formula (or maybe could do a macro if needed), but what I'm trying to do is basically this:

In cell B1:
1. Take a value from cell A1 in Workbook A.
2. Search for that value on ANY tab in ANY workbook in file path "P:\shared\2019" which has multiple subfolders (i.e. 02 2019, 03 2019, 04 2019, etc) - workbooks are mostly named by date (i.e. 01.01.19.xlsx) so theres no more than approx. 31 files per folder, but obviously it varies
3. Return the file name of the FIRST file the value is found on

In cell C1: same as above, but return the tab name of that file (3 possible tabs on each file)

In cell D1 & E1: same as above, but with the SECOND file the value is found on

In cell F1 & G1: same as above, but with the THIRD file the value is found on

There would be no more than 3 files it would be on and would repeat for all values in column A.
If this can be done with a formula, I could probably narrow down which subfolder in the file path to search in and just change it for each row as applicable, since I know the approximate frame each value should appear in.

 

[Rijnsent]

Hi Wadergirl,
that sounds like a macro to me, not something you could do in a simple function. Excel will have to open all those files and search in every one of them. In that macro, step 1 would be to list all files in that folder & subfolders, e.g. like so: https://www.mrexcel.com/forum/excel...-way-listing-folders-subfolders-contents.html
Next step: you could open every file for every search term, but that would be rather slow. Say you have 365 files and 100 search terms, that would mean opening and closing 36500 times, which takes a looooong time. So it's probably easier to open every file once and search for all the terms you want to search for and write those results in a sheet and as step 3 process them into the format you want.
Hope that gets you started, don't hesitate to post your code if you get stuck.
Kind regards,
Koen