How to perform Vlookup from Closed Workbook

How to perform a Vlookup from a closed workbook in Excel using VBA. We have earlier learnt how to Vlookup data, inventory management with Vlookup, getting multiple values using Vlookup, etc. Today we’ll learn how to get data from a closed workbook with Vlookup. The most important parameter in this Vlookup is the path to the closed workbook. Once we get the syntax for the path to the workbook right, everything else falls into place. Watch the video to learn how this goal can be achieved quickly and easily:


You can watch this video on YouTube also.

Here’s the complete VBA code to get data from a closed workbook with Vlookup:

Sub vlookupDatafromClosedWorkbook()

‘ How to Vlookup data from a closed workbook in a simpler manner
Range(“D2”) = “=VLOOKUP(RC[-3],(‘C:\exceltrainingvideos\[customers-email-mobile.xlsx]Sheet1’!R2C1:R10C3),2,FALSE)”
Selection.AutoFill Destination:=Range(“D2:D10”)
End Sub

Further reading:

Vlookup Function


Published by

Dinesh Kumar Takyar

Welcome to! My aim is to help you learn MS Excel including VBA. I always felt that a good way to learn something was to find solutions to problems in that domain. That is why I share these Excel videos with you. Mostly these are questions asked during my corporate training sessions. I've been training individuals and companies in computers since 1991. I did my B. Sc. (Vordiplom), M. Sc. (Diplom) and Ph.D. (Dr. rer. nat.) from Hamburg, Germany. The best thing about solving some of my visitor's questions is that I also gain new insights. For more Excel VBA solutions you may like to visit my YouTube channel: For a structured Excel VBA training course online you can visit:

2 thoughts on “How to perform Vlookup from Closed Workbook”

  1. Hello Sir

    Would you please teach us VBA formula that we extract the multiple reports and consolidate them into one sheet and pivot them which we require field.month on month report line items would be increased and VBA formula needs to apply all months if my report line items either increase or decrease kindly help on this Sir.Thank you very much for your help.

  2. Hey

    Cant get this to work
    With Sheets(“SYSTEM”)
    .Range(“Version”).Value = “=VLookup(‘Version’, ‘” & XLSPlacering & “[” & XLSfilen & “]SYSTEM’!$A$1:$B$100, 2, False)”
    End With

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.