VLookUp help!

[n9uns]

Hi all,

I have a scenario that I would greatly appreciate some help with.

- Can't use a simple VLOOKUP to get what I need because the name of the file is always changing as it includes a date. (ex: Activity MM-DD-YY.xlsx; where the MM-DD-YY is changing everyday)

- I have a CONCATENATE function in cell I1 that contains TEXT(WORKDAY(TODAY),1),"MM-DD-YY") to generate the date of the file that I need to reference for my VLOOKUP

Question is, how do I include cell I8, as the source of my VLOOKUP result, into my VLOOKUP? =VLOOKUP(A4,I8$A:$E,2,false) Hope this makes sense....

 

[Mike LH]

Hi,

You don't appear to have a sheet name in that vlookup or in I8 so I used sheet1. Try this

=VLOOKUP(A4,INDIRECT("'[" & I8 & "]Sheet1'!$A$1:$E$23"),2,FALSE)

or for full columns which for speed you should avoid.


=VLOOKUP(A4,INDIRECT("'[" & I8 & "]Sheet1'!A:E"),2,FALSE)

 

[n9uns]

Hi Mike LH, thank you for the quick response. Let's just name it Sheet1 for now...

I tried the first lookup formula you had suggested but it's returning a #REF error for me. I'm thinking it has to do with the link that I8 is returning.

I8 = C:\Server\Folder\Subfolder\SubfolderA\SubfolderB\FILE.xlsb

I tried removing C:\Server\Folder\Subfolder\SubfolderA\SubfolderB\FILENAME.xlsb from the CONCATENATE function and just having I8 read FILENAME.xlsb but that didn't work either...

Thanks!

 

[n9uns]

The question should be how do I include cell I1 (cell that includes the CONCATENATE formula) into my VLOOKUP.

 

[n9uns]

Just kidding, this worked wonderfully! Thank you Mike LH!