1

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.

pnuts
  • 58,317
  • 11
  • 87
  • 139
Jorn Theunissen
  • 191
  • 2
  • 14

1 Answers1

2

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.

Bathsheba
  • 231,907
  • 34
  • 361
  • 483