Method 1 – Applying VBA VLookup Function to Find Value from Another Workbook Without Opening It
1.1 Using VBA VLookup Function with Cells Property
We prepared a code to Vlookup from another workbook without opening it. We used the VBA Cells property of the Range object to pick the lookup value from the worksheet.
We included a code in the following section. Copy the code and insert it in a new module. You have to change the directory, workbook, and worksheet name in the code according to yours. Click the Run button.
Code:
Sub Apply_VLookup_Closed_Book()
'Declare Required Variables
Dim closed_wb As Workbook, open_wb As Workbook
Dim closed_rng As Range, open_rng As Range
Dim result1 As Variant
Dim result2 As Variant
Dim result3 As Variant
Dim result4 As Variant
Dim result5 As Variant
' Set the closed workbook and range
Set closed_wb = Workbooks.Open("C:\ExcelDemy\Sales Report.xlsx", ReadOnly:=True)
Set closed_rng = closed_wb.Worksheets("Sales").Range("B5:F14")
' Set the open workbook and range
Set open_wb = ThisWorkbook ' The open workbook
Set open_rng = open_wb.Worksheets(1).Range("B4:C9") ' The range in the open workbook
' Perform the VLOOKUP operation in the closed workbook and store the result in a variable
result1 = Application.VLookup(open_rng.Cells(2, 1), closed_rng, 3, False)
' Display the result in cell C7 of the open workbook
open_rng.Cells(2, 2).Value = result1
open_rng.Cells(3, 2).Value = result2
open_rng.Cells(4, 2).Value = result3
open_rng.Cells(5, 2).Value = result4
open_rng.Cells(6, 2).Value = result5
' Close the closed workbook
closed_wb.Close SaveChanges:=False
End SubCode Breakdown:
- We declared a Sub and necessary variables, for example, closed_wb & open_wb as Workbook, closed_rng, & open_rng as Range, result1 to 5 as Variant.
- We opened the worksheet from a closed workbook. For this, we used the Set statement with Workbooks.Open method where the ReadOnly parameter is True to open the workbook in read-only mode. Here, our closed workbook path is C:\ExcelDemy\Sales Report.xlsx and the range is B5:F14.
- We applied the VLookup function to lookup value. The VLookup function searches for the value open_rng.Cells(2, 1) in the table_array closed_rng and give the respective data from column 3 of the table. False symbolizes the match should be exact.
- We pasted the results in specific cells of the worksheet with Cells.Value property.
- We closed the workbook from where we extracted the data without changing anything.
Get the following output, as shown in the below video.
The following image shows the source dataset of the closed workbook from where we extracted the values.
1.2 Utilizing VBA VLookup Function with Range Property
Use a direct cell reference with the VBA Range property to specify the lookup value, which is a bit simpler to use.
Copy the attached code and make the necessary changes to the directory path workbook and worksheet names. Then, you can run the code.
Code:
Sub VLookup_Specified_Range()
Dim LookupValue1 As String
Dim LookupValue2 As String
Dim LookupValue3 As String
Dim LookupValue4 As String
Dim LookupValue5 As String
Dim LookupRange As Range
Dim ResultRange As Range
Dim ExternalWorkbook As Workbook
Set ExternalWorkbook = Workbooks.Open("C:\ExcelDemy\Sales Report.xlsx")
' replace "C:\Users\SHIMUL\OneDrive\Desktop\Softeko_home\95-0070\ales Report.xlsx" with the file path and name of the external workbook you want to lookup values from
Set LookupRange = ExternalWorkbook.Worksheets("Sales").Range("B4:F14")
' replace "Sales" with the name of the worksheet that contains the lookup and result ranges in the external workbook
LookupValue1 = ThisWorkbook.Worksheets("Specified_Range").Range("B5").Value
LookupValue2 = ThisWorkbook.Worksheets("Specified_Range").Range("B6").Value
LookupValue3 = ThisWorkbook.Worksheets("Specified_Range").Range("B7").Value
LookupValue4 = ThisWorkbook.Worksheets("Specified_Range").Range("B8").Value
LookupValue5 = ThisWorkbook.Worksheets("Specified_Range").Range("B9").Value
' replace "Specified_Range" with the name of the worksheet that contains the lookup value in the current workbook
ThisWorkbook.Worksheets("Specified_Range").Range("C5").Value = Application.VLookup(LookupValue1, LookupRange, 3, False)
ThisWorkbook.Worksheets("Specified_Range").Range("C6").Value = Application.VLookup(LookupValue2, LookupRange, 3, False)
ThisWorkbook.Worksheets("Specified_Range").Range("C7").Value = Application.VLookup(LookupValue3, LookupRange, 3, False)
ThisWorkbook.Worksheets("Specified_Range").Range("C8").Value = Application.VLookup(LookupValue4, LookupRange, 3, False)
ThisWorkbook.Worksheets("Specified_Range").Range("C9").Value = Application.VLookup(LookupValue5, LookupRange, 3, False)
ExternalWorkbook.Close False
' set "False" to "True" if you want to save changes to the external workbook before closing it
End SubCode Breakdown:
- We opened a closed workbook Sales Report.xlsx, and set a variable LookupRange for range B4:F14 of the Sales worksheet from where we will extract the value.
- We set LookupValue1 with a value from range B5 of the Specified_Range sheet.
- We used the VLookup function to paste the value in a specific cell. The VLookup function searches for LookupValue1 in table_array LookupRange and gives respective column 3 output from the table_array. False means an exact match for looking for value.
- Lastly, we closed the external workbook.
The image shows the source dataset of the closed workbook from where we extracted the values.
After running the code, you’ll get the following output, as shown in the below video.
1.3 Incorporating Excel VLOOKUP Function with VBA Code
Another much simpler way to do the same task is to insert the general Excel VLOOKUP function in the worksheet cell with the help of VBA code. Wiithout further delay, let’s jump to the procedures.
All you have to do is insert the attached code in a new module and run it.
Code:
Sub VLOOKUP_Closed_Workbook()
ThisWorkbook.Worksheets("VLOOKUPwithVBA").Range("C5").Formula = "=VLOOKUP(B5,'C:\ExcelDemy\[Sales Report.xlsx]Sales'!B4:F14,3,FALSE)"
ThisWorkbook.Worksheets("VLOOKUPwithVBA").Range("C6").Formula = "=VLOOKUP(B6,'C:\ExcelDemy\[Sales Report.xlsx]Sales'!B4:F14,3,FALSE)"
ThisWorkbook.Worksheets("VLOOKUPwithVBA").Range("C7").Formula = "=VLOOKUP(B7,'C:\ExcelDemy\[Sales Report.xlsx]Sales'!B4:F14,3,FALSE)"
ThisWorkbook.Worksheets("VLOOKUPwithVBA").Range("C8").Formula = "=VLOOKUP(B8,'C:\ExcelDemy\[Sales Report.xlsx]Sales'!B4:F14,3,FALSE)"
ThisWorkbook.Worksheets("VLOOKUPwithVBA").Range("C9").Formula = "=VLOOKUP(B9,'C:\ExcelDemy\[Sales Report.xlsx]Sales'!B4:F14,3,FALSE)"
' change the file path according to your one 'C:\Users\SHIMUL\OneDrive\Desktop\Softeko_home\95-0070\[Sales Report.xlsx]Vlookup_Apply'
'Also change the cell reference according to your worksheet
End SubWe inserted the VLOOKUP formula with the help of VBA. The following part inside the VLOOKUP formula navigates the worksheet of the closed workbook.
The following image shows the source dataset from where we extracted the values.
Get the following output, as shown in the below video.
1.4 Employing VBA VLookup Function with InputBox
Change the code a bit to take the value to look for from the InputBox and give the lookup value in a MsgBox.
Copy the attached code into a new module, change the file directory as necessary, and run the code.
Code:
Sub VLookup_msgbox()
Dim LookupValue As String
Dim LookupRange As Range
Dim ResultRange As Range
Dim ExternalWorkbook As Workbook
Set ExternalWorkbook = Workbooks.Open("C:\ExcelDemy\Sales Report.xlsx")
' Replace "C:\Users\SHIMUL\OneDrive\Desktop\Softeko_home\95-0070\ales Report.xlsx" with the file path and name of the external workbook you want to lookup values from
Set LookupRange = ExternalWorkbook.Worksheets("Sales").Range("B4:F14")
' Replace "Sales" with the name of the worksheet that contains the lookup and result ranges in the external workbook
LookupValue = InputBox("Enter the Value for VLookup", "Input Box")
VLookup_Result = Application.VLookup(LookupValue, LookupRange, 3, False)
ExternalWorkbook.Close False
' Set "False" to "True" if you want to save changes to the external workbook before closing it
MsgBox "The VLookup Value(Quantity) is " & VLookup_Result
End SubIn the code, we set an input box whose value we used in the VLookup formula. We showed the Vlookup result value in a MsgBox.
The following image shows the source dataset of the closed workbook from where we extracted the values.
After running the code, you’ll get the following output, as shown in the below video.
Method 2 – Using VBA Index Match Functions to Vlookup from Another Workbook Without Opening
As an efficient alternative to using the VBA VLookup function, we can use the VBA Index and Match functions together to vlookup in another workbook without opening it in Excel. Why not use them in the code?
Copy the attached code into a new module, change the directory and sheet name, and run the code.
Code:
Sub Index_Match_ExternalWorkbook()
Dim LookupValue1 As String
Dim LookupValue2 As String
Dim LookupValue3 As String
Dim LookupValue4 As String
Dim LookupValue5 As String
Dim LookupRange As Range
Dim ResultRange As Range
Dim ExternalWorkbook As Workbook
Set ExternalWorkbook = Workbooks.Open("C:\ExcelDemy\Sales Report.xlsx")
' replace "C:\Users\SHIMUL\OneDrive\Desktop\Softeko_home\95-0070\ales Report.xlsx" with the file path and name of the external workbook you want to lookup values from
Set LookupRange0 = ExternalWorkbook.Worksheets("Sales").Range("B5:B14")
Set LookupRange = ExternalWorkbook.Worksheets("Sales").Range("D5:D14")
' replace "Sales" with the name of the worksheet that contains the lookup and result ranges in the external workbook
LookupValue1 = ThisWorkbook.Worksheets("Index_Match").Range("B5").Value
LookupValue2 = ThisWorkbook.Worksheets("Index_Match").Range("B6").Value
LookupValue3 = ThisWorkbook.Worksheets("Index_Match").Range("B7").Value
LookupValue4 = ThisWorkbook.Worksheets("Index_Match").Range("B8").Value
LookupValue5 = ThisWorkbook.Worksheets("Index_Match").Range("B9").Value
' Replace "Vlookup_Apply" with the name of the worksheet that contains the lookup value in the current workbook
ThisWorkbook.Worksheets("Index_Match").Range("C5").Value = Application.Index(LookupRange, Application.Match(LookupValue1, LookupRange0, 0))
ThisWorkbook.Worksheets("Index_Match").Range("C6").Value = Application.Index(LookupRange, Application.Match(LookupValue2, LookupRange0, 0))
ThisWorkbook.Worksheets("Index_Match").Range("C7").Value = Application.Index(LookupRange, Application.Match(LookupValue3, LookupRange0, 0))
ThisWorkbook.Worksheets("Index_Match").Range("C8").Value = Application.Index(LookupRange, Application.Match(LookupValue4, LookupRange0, 0))
ThisWorkbook.Worksheets("Index_Match").Range("C9").Value = Application.Index(LookupRange, Application.Match(LookupValue5, LookupRange0, 0))
ExternalWorkbook.Close False
' set "False" to "True" if you want to save changes to the external workbook before closing it
End SubCode Breakdown:
- Set the range LookupRange0 with data from range B5:B14 of the Sales sheet of the Closed workbook “Sales Report” which is later used in the Match function.
- Set LookupRange with the range D5:D14 of the Sales sheet of the closed workbook from where the result value will come from with the Index function.
- Set LookupValue1 with a value from cell B5 which means the values to look for from our active sheet.
- We applied the previously mentioned data as the arguments of the Index & Match functions to get the lookup result. The Match function searches for LookupValue1 in the table range LookupRange0 and gives the respective row number. 0 denotes the match should be exact. The Index function gives the data from the row number(which comes from the Match function) of the range LookupRange.
The following image shows the source dataset of the closed workbook from where we extracted the values.
After running the code, you’ll get the following output, as shown in the below video.
How to Vlookup in Same Worksheet Within Same Workbook Using VBA VLookup Function in Excel
Vlookup from the same worksheet is a simple task. We will show you how to do it with VBA.
Insert the added code in a new module and run it. You may need to change the workbook and sheet name.
Code:
Sub VLookup_Same_Sheet()
ThisWorkbook.Worksheets("VLookup_Same_Sheet").Range("C17") = Application.VLookup(ThisWorkbook.Worksheets("VLookup_Same_Sheet").Range("B17"), ThisWorkbook.Worksheets("VLookup_Same_Sheet").Range("B5:F14"), 3, False)
End SubWe used the simple VBA VLookup function with all ranges from our active sheet.
How to Extract Value from Different Sheet of Same Workbook Using VBA VLookup in Excel
We can also perform Vlookup from another sheet of the active workbook. Let’s do it with the VBA code. We used the “Sales” sheet of our active workbook for the lookup.
Copy the attached code into a new module and run it. Change the sheet name according to your preference.
Code:
Sub VLookup_Different_Sheet()
ThisWorkbook.Worksheets("Vlookup_Other_Sheet").Range("C5") = Application.VLookup(ThisWorkbook.Worksheets("Vlookup_Other_Sheet").Range("B5"), ThisWorkbook.Worksheets("Sales").Range("B5:F14"), 3, False)
ThisWorkbook.Worksheets("Vlookup_Other_Sheet").Range("C6") = Application.VLookup(ThisWorkbook.Worksheets("Vlookup_Other_Sheet").Range("B6"), ThisWorkbook.Worksheets("Sales").Range("B5:F14"), 3, False)
ThisWorkbook.Worksheets("Vlookup_Other_Sheet").Range("C7") = Application.VLookup(ThisWorkbook.Worksheets("Vlookup_Other_Sheet").Range("B7"), ThisWorkbook.Worksheets("Sales").Range("B5:F14"), 3, False)
ThisWorkbook.Worksheets("Vlookup_Other_Sheet").Range("C8") = Application.VLookup(ThisWorkbook.Worksheets("Vlookup_Other_Sheet").Range("B8"), ThisWorkbook.Worksheets("Sales").Range("B5:F14"), 3, False)
ThisWorkbook.Worksheets("Vlookup_Other_Sheet").Range("C9") = Application.VLookup(ThisWorkbook.Worksheets("Vlookup_Other_Sheet").Range("B9"), ThisWorkbook.Worksheets("Sales").Range("B5:F14"), 3, False)
End SubWe included the table_array for the VLookup function from another sheet of the active workbook. We changed the sheet name reference for that case.
The VLookup results are indicated in the following image.
How to Vlookup from Another Workbook Without VBA in Excel
We can easily use a simple Excel VLOOKUP function to get the lookup value from another workbook without opening it. Use the following formula to do the trick. For the Vlookup value we used the following dataset from the “Sales” worksheet of a closed workbook “Sales Report”.
=VLOOKUP(B5,'C:\ExcelDemy\[Sales Report.xlsx]Sales'!$B$4:$F$14,3,FALSE)
Change the range of references and workbook directory according to your preferences.
How to Get Data from Another Workbook in Excel VBA Without Opening It
It’s pretty simple to get data from another workbook without opening it. Let’s use the VBA code to do it.
Insert the following code and run it. You may need to change the file directory, workbook, and worksheet name and range according to your needs.
Code:
Sub Copy_Data_Closed_Workbook()
Workbooks.Open("C:\ExcelDemy\Sales Report.xlsx", _
True, True).Worksheets("Sales").Range("B5:F14").Select
Selection.Copy
Windows("Applying VLOOKUP on Another Closed Workbook.xlsm").Activate
Sheets("Copied_Sheet").Select
Range("B5").Select
ActiveSheet.Paste
Workbooks.Open("C:\ExcelDemy\Sales Report.xlsx").Close
End SubWe selected a specific range from a closed workbook with the Select property. We copied the selected range with the Copy property. We selected range B5 of our active worksheet. We pasted the copied value with the Paste property.
The Copied data from the closed workbook are indicated in the following image.
Things to Remember
- In some cases, you have to be careful about specific cell references with the”$” sign.
- Verify the lookup value is present in the lookup table.
- You have to change the file path directory and workbook name, worksheet name, and different ranges in the attached codes according to your file environment.
- It’s quite simple to include references in Excel formulas. For example, if you type VLOOKUP afterward you can select the specific cell from any sheet for lookup value or table_array etc. Excel will automatically include sheet reference if it’s not an active sheet.
- Note that the workbook name is enclosed with a square bracket. Also, the workbook name and sheet name are enclosed with a single quotation (‘) which is required for names having spaces and punctuation signs.
Frequently Asked Questions
1. Can I use VBA VLookup to search for a value in multiple workbooks at once?
Sure, you can utilize VBA VLookup to search for a value in multiple worksheets at once. To do this, you have to combine all data into a single table in a worksheet and then apply the VLookup function.
2. How can I use VBA to automate the process of performing a vlookup in another workbook?
Certainly, you can automate the process utilizing VBA. For this, you can create a macro button or user interface element and then assign the VBA code to the element.
3. Are there any alternatives to VBA to vlookup from another workbook without opening?
Undoubtedly! Simply, you can apply the Excel VLOOKUP function that we discussed earlier. Furthermore, you can incorporate Power Query to do the same task.
Wrapping Up
- In the first method, we did vlookup from a closed workbook with the VBA VLookup function. We used a different approach for doing that, we used the Cells property in the first submethod, the second submethod was for putting vlookup value in a specific range, and in the third one we inserted simple VLOOKUP formula in the worksheet with VBA, in the fourth one we used input box for the lookup value of Vlookup function also we displayed the result with MsgBox.
- In the second method, we used the Index and Match functions to do the same work as VLookup.
- Later on, we discussed the ways to vlookup from the same as well as different sheets.
- We also discussed the way to do vlookup without VBA.
- In addition, we showed how to get data from another workbook with VBA without opening the workbook.
Download Practice Workbook
You can download these practice workbooks from here.
Related Articles
- Use Excel VBA VLOOKUP to Find Values in Another Worksheet
- How to Use Excel VBA VLookup Within Loop
- Excel VBA: Working with If, IsError, and VLookup Together
