Forum Discussion
VLOOKUP and using Table_Array for looking up a file
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).