Creating a dynamic VLOOKUP

Question

I need to create a dynamic VLOOKUP because the Years and month in a filename and sheetname changes.

eg.  For December 2013, the file name is "C:\Metrics[2013 - 2014 RTL Monthly Metrics.xlsx" and the correct sheetname in this file is "November".

       For January 2014, the file name is still "C:\Metrics[2013 - 2014 RTL Monthly Metrics.xlsx" but the correct sheetname in this file is "December".

  In April, we go to a new fiscal year.  So,

      For May 2014, the file name would be "C:\Metrics[2014 - 2015 RTL Monthly Metrics.xlsx" and the correct sheetname in this file is "April".

I need to create a VLOOKUP that ends up like this;

VLOOKUP("Fred",'C:\Metrics[2013 - 2014 RTL Monthly Metrics.xlsx]November'!$A$3:$D$13,2,0)

I have this formula, but I get a "#Value!" error.

=VLOOKUP("Fred",CONCATENATE("'C:\Metrics[" & YEAR(NOW()) & " - " & YEAR(NOW())+1 & " RTL Monthly Metrics.xlsx]" & TEXT(EOMONTH(NOW(),-1),"MMMM") & "'!$A$3:$D$13"),2,0)

I evaluated the formula and everything in the CONCAT is created  correctly, but when it goes to add the ",2,0)" is when I get the "#Value!" error.

I just don't see where the error is.

1. The path and file name exists and is correct.

2. "Fred" exists on the "November" sheet within the $A$3:$D$13 range

3.  If I hardcode VLOOKUP("Fred",'C:\Metrics[2013 - 2014 RTL Monthly Metrics.xlsx]November'!$A$3:$D$13,2,0)  it works.

Answer 1

Hi,

Substitute CONCATENATE( function with INDIRECT(.  Also, for the INDIRECT() function to work fine you will have to ensure that the other workbook is open.  If you want to fetch data from a closed Excel file, then you may refer to my solution at this link

• http://www.ashishmathur.com/extract-data-from-multiple-cells-of-closed-excel-files/

Hope this helps.