VLOOKUP and using Table_Array for looking up a file

Jit_Bristol A couple things to note:

using the INDIRECT() means the file must be open which also means you don't need the full address (just the file name is sufficient)

if you really want to avoid INDIRECT() you can use the hard coded address to essentially copy the needed data from each of those sheets into this workbook and then do the look up within this workbook.  I hesitate to mention this as you indicated there are many other workbooks and I fear this could cause significant lags and performance issues.  But in concept you could, in Client Setup instead of just the text path reference you have each row become 2 rows with the client in the first column and then in the second column you have these formulas

=TRANSPOSE('\\Bxxxfp1c\wrkgroup\XX ESS\FXXXX_Upgrade_Checklist\Tranche 02\[T02-Laxxxx-CM Checklist.xlsx]Sheet1'!$D1:$D1000)

=TRANSPOSE('\\Bxxxfp1c\wrkgroup\XX ESS\FXXXX_Upgrade_Checklist\Tranche 02\[T02-Laxxxx-CM Checklist.xlsx]Sheet1'!$J1:$J1000)

Now I chose rows 1:1000 but you might need more (or less would be better or you dynamically find the size of the data and use that).  But now that you have the relevant data you can now use HLOOKUP with an OFFSET or INDEX-MATCH combo to find the data you want.  You could also (or if the data sets are really big might have to) reorganize the Client Setup sheet to be in columns so clients go across row 1 and every 2 columns is a new client with the source data copied over (i.e. remove the TRANSPOSE from the above equations and put them in row 2 under the client name)

last, another option would be to create a custom macro that would update the data based on a particular event or events (e.g. on workbook open and if they update a particular cell or cells)