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 '18 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 '18 at 19:39

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

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.