Forum Discussion

Jit_Bristol's avatar
Jit_Bristol
Copper Contributor
Jan 04, 2021
Solved

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

6 Replies

No RepliesBe the first to reply

Resources