Search code examples
excelvlookupexcel-indirect

Dynamic VLOOKUP with INDIRECT

I need to perform a VLOOKUP with INDIRECT to a different workbook and to a specific sheet.

The name and the sheetname should be dynamically entered inside the direct function (they can be found in cell H2 and H3). This is where I am having trouble. I am trying the following:

=(VLOOKUP(A2,INDIRECT("'["&H2&".xls]"&H3&"'!$1:$100000"),4,FALSE))

The 'cells' are not being recognised and seem to be part of the string rather than variables.

Solution

  • Using a volatile function within VLOOKUP will cause you all sorts of headaches.

    I suggest you use an intermediate cell containing INDIRECT("'["&H2&".xls]"&H3&"'!$1:$100000"). This at least forces calculation to take place in a certain order.