Using VLOOKUP with array in different workbook and referencing the filename

[Binoy]

Hey all. I've got 2 different workbooks I want to compare the data between, sort of an audit thing. In order to get specific data for comparison I was thinking of using a vlookup function. That's no problem. My issues arrive because the names of the workbooks I need to look up the data in change based on what days I'm looking up.

Now I've got a macro already opened which puts the path and filename as a string in a range name cell. Is there a way to incorporate the range name into the vlookup function? Sort of like:

=vlookup(Lookup_value,'[RangeName]Sheet!Array]

If I simply type in the function with that layout it just throws an open dialog box at me to specify the file, but I want it to use the path specified in the Range name cell.

If there's a better way to do it I'm open to suggestions.

Thanks,

Binoy

[Ken Puls]

Hi Binoy,

Typically, what I do is create a named range via code and then just use that in my lookup or index/match formulas. It's easy enough to do, and I find that it makes placing the formulas in the worksheets SOOO much easier, particularly if you need to set anything using RC referencing. (Maybe just me, but there you have it.)

This is untested, but I think it should work:

Dim wbTarget As Workbook
Set wbTarget = Workbooks("TheWorkbookWithTheTable.xls")
' Set the name
ThisWorkbook.Names.Add "tblData", wbTarget.Worksheets(1).Range("A1:G400")
' Put your vlookup here
ActiveCell.FormulaR1C1 = "=vlookup(RC[-1],tblData,false)"

Sometimes I have need to do the above, then paste the values and get rid of the range names as well. To delete the names:

' Delete the name
On Error Resume Next
For Each nm In ThisWorkbook.Names
    If nm.Name = "tblData" Then nm.Delete
Next nm
On Error GoTo 0

HTH,

[Binoy]

Thanks for the help, I'll try that out.