January 13, 2018

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


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 decreased.so 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

Comments are closed.