I have been using VLOOKUP to access information from .csv files for years using the format:
=VLOOKUP(B$3,‘file:ffffdddd.csv’#$Sheet1.$B$6:$F$350,2,0)
where ffff is a path/filename that never changes, and dddd is a date that is different for every file.
My problem is that I use this for thousands of .csv files. To use the formula for multiple dates I have to copy it to multiple cells and then manually change the dddd part of the formula in each cell. Then I copy and paste special to convert the result to a permanent number value.
What I would like to do is replace ffffdddd with a hyperlink that I can create with CONCATENATE() so it reads a date from column 1 and creates the necessary filename to replace ffffdddd.csv, something similar to
=VLOOKUP(B$3, (D$7)#$Sheet1.$B$6:$F$350,2,0)
where (D$7) contains the concatenated filename created from a date in D$1:
=HYPERLINK(CONCATENATE(path\filename_and_date_strings_created_from_D$1))
I have no problem creating a functioning hyperlink (i.e. the contents of (D$7)) that opens a .csv file, but I cannot figure out a way to implement the VLOOKUP() using it. I’ve tried concatenating $SHEET1.arrayrange directly to the filename but the hyperlink won’t work. Also played around with INDIRECT().
Any ideas?