I need to use a vlookup to find some information in another workbook where the worksheet will be a variable, which I would like to get from a cell reference.

The sheets will be suppliers names, so for example:

Sheet1: supplier_1 Sheet2: supplier_2

The Vlookup will look something like this:

=VLOOKUP(A2,'[Suppliers List]B2'!$A:$C,3, False)

Column A will be a list of product ID's and column B will be the suppliers name, so in the example above B2 would equal supplier_1.

Is it possible to put the reference into the formula directly in excel or would I need to use VBA?

  • Look at the INDIRECT function. It creates a cell reference from text and formulas.
    – fixer1234
    May 22, 2018 at 9:42
  • If Lee's answer solves the problem for you, consider accepting it (by clicking the checkmark next to it). That helps other users by identifying the status as the problem being solved and that there is a proven solution here. It also awards a little rep to both of you for the effort.
    – fixer1234
    May 25, 2018 at 19:39

1 Answer 1


You can use this forumula:

=VLOOKUP(A2,INDIRECT("'[Suppliers List.xlsx]"&B2&"'!"&"A"&":"&"C"),3,FALSE)

Supplier List Workbook:

enter image description here enter image description here

Result: enter image description here

But you need to open Suppliers List Workbook first, or you will get the error: #REF!

enter image description here

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .