Using Xlookup to pull data from variable excel files (that are not open)

[therealjdj]

Hey all,
I have around 200 excel files that are named as such: unique_ID_number.xlsx

I need to pull two data points from each of these files. So I have a master sheet that lists all the unique IDs. I need a way to automatically pull this data but the only solution I can find, requires me to open all 200 excels - sounds like it's due to using INDIRECT. How could this be avoided?

Here is what I have:

=XLOOKUP(J1,INDIRECT(CONCATENATE("'[",$D4,".xlsx]Sheet1'!$A$1:$A$15")),INDIRECT(CONCATENATE("'[",$D4,".xlsx]Sheet1'!$F$1:$F$15")),"ERROR",0,1)

In this case I am looking for value J1
and
the unique ID is listed in column D

Help please!

Thanks all

 

[Fluff]

How could this be avoided?

Unfortunately it can't, unless you actually specify the workbook name (& full file path) in the formula.