VLOOKUP and using Table_Array for looking up a file

[Jit_Bristol]

Hi, I'm amending an existing VLOOKUP command which reads in status data (for a Company & Tranche) from a file stored on another server and displays the data. The Table_Array is hard-coded eg :

=IFERROR(VLOOKUP($B19,'\\Bxxxfp1c\wrkgroup\XX ESS\FXXXX_Upgrade_Checklist\Tranche 02\[T02-Laxxxx-CM Checklist.xlsx]Sheet1'!$D:$J,7,FALSE),"NO DATA").

I want to replace the Table_Array e.g. "'\\Bxxxfp1c\wrkgroup\XX ESS...." with a VLOOUP on a sheet in my spreadsheet (called Client Setup) where I can define the file and path for each company and tranche, e.g.

 =IFERROR(VLOOKUP($B15,VLOOKUP(E2,'Client Setup'!$A$4:$O$220,11,FALSE),7,FALSE),"NO DATA").

If I hard code the value the VLOOKUP works but not in the syntax above, I just get back "NO DATA"  (I've checked the VLOOKUP on E2 is correct.).  Can someone explain what I'm doing wrong?

The reason for doing this is that I want to remove the hard coding and instead users just need to maintain the Client Setup file without changing the code (other than Company Name/Tranche).

[#2 · Accepted solution]

@Jit_Bristol the return from your inner VLOOKUP is just a string value not a proper reference.  You could add an INDIRECT() but the file will need to be open for that INDIRECT to work:

=IFERROR(VLOOKUP($B15,INDIRECT(VLOOKUP(E2,'Client Setup'!$A$4:$O$220,11,FALSE)),7,FALSE),"NO DATA").

[#3]

Hiya
Thanks for the speedy reply and also why the syntax as I had it doesn't work. I tried the INDIRECT syntax as suggested and as you said the file needs to be open for the command to work. This would be problematic as there would be multiple users and not sure it would be practical to ask them to open each file required. I haven't got too much to spend it on it right now so will leave code as it is and have to hard code the file names in.
Kind Regards,
Jitesh

[#4]

@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)

[#5]

@mtarler Thanks for the follow-up. As this spreadsheet will cover multiple clients and tranches there will be a large number of workbooks created so I think performance would be an issue. Also this specific task is one of many other, non-spreadsheet, related tasks I'm doing for the client so time willing I will try out your suggested method (I like a technical challenge!).  Appreciate the time you have given to come back with the alternative solution.

[#6]

You're welcome and best of luck.

[#7]

@mtarleri can hlep you hàm vlookup trong excel