Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

Use Excel VBA VLOOKUP to Find Values in Another Worksheet

Circumstances may arise when you need to use certain VBA codes that execute the operation from different worksheets, like you may need to use VBA VLOOKUP in code to find lookup value from another sheet. Today we are going to show you how to use the Excel VBA VLOOKUP in another worksheet.


Download Workbook

You are welcome to download the Excel file and practice while reading this article.


5 Methods to Use VBA VLOOKUP to Find Values in Another Worksheet in Excel

Here, we have a dataset that contains the Author Name, DOB, and Famous for columns. This set of data is stored in the sheet called “Data”.

Data Sheet for Excel VBA VLOOKUP in Another Worksheet

On another sheet called “Details”, we have stored the Birth Place for the authors.

Furthermore, we will execute the VLOOKUP operation through the VBA code within the sheets. Here, we used Excel 365. You can use any available Excel version.

Here, note that this is a basic dataset to keep things simple. In a real-life scenario, you may encounter a much larger and more complex dataset.

Details Sheet for Excel VBA VLOOKUP in Another Worksheet


1. Using VLOOKUP for Static Changes in Another Worksheet

Here, we will show you how you can use VBA VLOOKUP in another worksheet in such a way that for every execution we need to change that manually.

To show you examples, here we have introduced a Birth Place column to the author lists dataset. We aim to find the birthplace of each writer that is listed in the “Details” sheet.

Hope you have noticed that this sheet’s name is “Static Changes”, we have copied the data from the “Data” sheet here to keep that sheet intact and understand the changes easily.

 Excel VBA VLOOKUP in Another Worksheet for Static Changes

Steps:

  • First of all, to open the VBA Editor window, we will press the ALT+F11 keys.

Here, you can go to the Developer tab >> select Visual Basic to open the VBA Editor window.

Pressing ALT+F11 Key for Excel VBA VLOOKUP in Another Worksheet

At this point, a VBA Editor window will appear.

  • Then, from the Insert tab >> select Module.

After that, we will write the following code in the Module.

Sub VLookup_Static_Change()

Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Static Changes")
With ws
   .Range("E5").Formula = "=VLOOKUP(B5,Details!$B:$C,2,0)"
End With

End Sub

Code Breakdown

  • Here, we declare Vlookup_Static_Change as the Sub.
  • Within this sheet named Static Changes, in Range(“E5”), we want to place the result. Hope you have understood that the result will be derived from the VLOOKUP formula where we mentioned the required values..
  • Inside the VLOOKUP we have provided the author name as lookup_value where the “Details” sheet data was the lookup_array and the column_index-number is 2 which will return the values from the Birth Place

  • Afterward, we will Run the code (for your convenience we have highlighted the Run button in the earlier image).

As a result, we have found the birthplace of the author Arthur Conan Doyle in cell E5.

  • Afterward, if you want to find the result for the second author from our list, you need to modify the code a bit: change the respective cell references.

As a result, you can see the complete Birth Place column.

Read More: Excel VLOOKUP to Return Multiple Values Vertically

Similar Readings


2. Applying VLOOKUP to Change Selection of Another Worksheet in Excel

In the earlier method, we have shown how to use Excel VBA VLOOKUP in another worksheet for static changes. That may seem irritating since we need to change the cell references every time before executing the code.

To get rid of the problem we will see how to use VLOOKUP for a selected cell. For this example, we have copied the data to the “Change Selection” sheet.

In our example, we will select a cell from the Birth Place column and our code will derive the birthplace of the author from that row.

Probably you have thought about how a mere selection will execute the code, absolutely correct. Only selecting a cell will not trigger the code. To execute the code, we need a button or shortcut key. Let’s proceed with the shortcut key.

Applying Excel VBA VLOOKUP in Another Worksheet for Change Selection

Steps:

  • In the beginning, From the Developer tab select the Record Macro option.

This will bring the Record Macro dialog box in front of you.

  • After that, we set the Macro name as Vlookup_shortcut.
  • Then, we set CTRL+SHIFT+J in the Shortcut key box.

Here, you can set the Macro name and Shortcut key according to your choices.

Using Record Macro for Excel VBA VLOOKUP in Another Worksheet

Afterward, we followed the Steps described in Method-1 to bring out the VBA Editor window.

  • Now in Microsoft Visual Basic for Applications, open the module for the Vlookup_shortcut macro and write the following code  
Sub Vlookup_shortcut()
'
' Keyboard Shortcut: Ctrl+Shift+J
'

    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-3],Details!R5C2:R11C3,2,0)"
End Sub

VLOOKUP Shortcut for Excel VBA VLOOKUP in Another Worksheet

Code Breakdown

  • Once we have set the macro name and shortcut key then the outline of the SUB procedure has been created. We have only provided a single line of code within it.
  • The ActiveCell denotes which cell is going to be the selected one. Here R and C are for row and column.
  • Our lookup_value is at the 3 columns left to the selected cell that’s why we have written RC[-3].
  • In general, the lookup_array cell reference is B5:C15, which means row 5 column 2 to row 11 column 3, so we have set R5C2:R11C3 (from the “Details” sheet).

  • Then, save the code >> go back to your Worksheet.
  • After that, select cell E5 >> press the shortcut key CTRL+SHIFT+J.

Therefore, we have found the result we were looking for.

Along with that, you can see the formula in the Formula Bar.

  • Afterward, we will drag down the formula with the Fill Handle tool.

Hence, you can see the complete Birth Place column.

Read More: VLOOKUP Example Between Two Sheets in Excel


3. Use VLOOKUP to Find All Values from Another Worksheet

In this method, we will see the VBA code to find the value for the entire data at once. This method is a quick and handy method for Excel VBA VLOOKUP in another worksheet.

Again, to keep the changes understandable, we have copied the dataset to the “Entire At Once” sheet.

Use of Excel VBA VLOOKUP in Another Worksheet to Change at Once

Steps:

  • First of all, we followed the Steps described in Method-1 to bring out the VBA Editor window.

Then, we write the following code.

Sub Vlookup_Entire_Sheet()

Dim authorWs As Worksheet, detailsWs As Worksheet
Dim authorsLastRow As Long, detailsLastRow As Long, x As Long
Dim dataRng As Range

Set authorWs = ThisWorkbook.Worksheets("Entire At once")
Set detailsWs = ThisWorkbook.Worksheets("Details")

authorsLastRow = authorWs.Range("B" & Rows.Count).End(xlUp).Row
detailsLastRow = detailsWs.Range("B" & Rows.Count).End(xlUp).Row

Set dataRng = detailsWs.Range("B5:C" & detailsLastRow)

For x = 2 To authorsLastRow
    On Error Resume Next
    authorWs.Range("E" & x).Value = Application.WorksheetFunction.VLookup( _
    authorWs.Range("B" & x).Value, dataRng, 2, False)
Next x

End Sub

Code Breakdown

  • Here we have declared the worksheet variables to store the sheets. Then used authorsLastRow and detailsLastRow to get the number of the last row of both sheets to make the list dynamic.
  • While defining these two variables we have set the range to start from cell B and keep counting to the end of it (until a value is inserted)
  • The dataRng variable to set the lookup_array
  • Then a FOR loop goes across the cells and derives the values.

  • After that, we will Save the code >> press the Run the code.

Therefore, we have found the Birth Place of all the authors at once.

Read More: Excel Dynamic VLOOKUP (with 3 Formulas)


4. Using VLOOKUP to Get Values for a Particular Sheet

In this method, we will use the VBA VLOOKUP for a particular worksheet. This method is helpful when we want the outcome of the VBA in a definite worksheet.

Here, to keep the changes understandable, we have copied the dataset to the “Definite Sheet”.

Applying Excel VBA VLOOKUP in Another Worksheet for Particular Sheet

Steps:

  • First of all, we followed the Steps described in Method-1 to bring out the VBA Editor window.
  • Then, we write the following code.
Sub Lookup_Birth_Place()

   Dim ws_worksheet As Worksheet
   Dim rng_range As Range
   Dim start_Name As Stringstart_Name = Range("B5")
   Set ws_worksheet = Sheets("Details")
   Set rng_range = ws_worksheet.Range("B5:C11")
   Sheets("Definite Sheet").Range("E5"). _
   Formula = Application.WorksheetFunction.VLookup(start_Name, rng_range, 2, False)

End Sub

Code Breakdown

  • We declare Lookup_Birth_Place as the Sub
  • From the Details sheet, it will look up the values for the Definite Sheet.
  • Next used Range(“E5”), to set the cell to insert the result. Hope you have understood that the result will be derived from the VLOOKUP
  • Inside the VLOOKUP we have provided the author name as lookup_value where the “Details” sheet data was the lookup_array and the column_index-number is 2 which will return the values from the Birth Place

  • After that, Save the code >> Run the code.

Note that this code will only execute the result in a particular sheet. Even if we run the code in a different sheet, the result will be shown in the Definite Sheet.

As a result, we have found the birthplace of the author Arthur Conan Doyle in cell E5.

  • Now if you want to find the result for the second author from our list, you need to modify the code a bit: change the respective cell references.

Hence, you can see the complete Birth Place column.


5. Using VBA If…Else Statement to VLOOKUP Value in Another Worksheet

In this method, we will use the If..Then..Else statement for Excel VBA VLOOKUP in another worksheet.

Here, to keep the changes understandable, we have copied the dataset to the “Definite Sheet”.

Using If Else Statement for Excel VBA VLOOKUP in Another Worksheet

Steps:

  • First of all, we followed the Steps described in Method-1 to bring out the VBA editor window.
  • Then, we write the following code.
Sub search_birth_place()
  Dim ws_1 As Worksheet, ws_2 As Worksheet
  Dim search_birthplace As Variant

  Set ws_1 = Worksheets("Details")
  Set ws_2 = Worksheets("IF ELSE Statement")

  On Error Resume Next
  search_birthplace = Application.WorksheetFunction.VLookup(ws_2.Range("B5"), _
  ws_1.Range("B5:C11"), 2, False)
  On Error GoTo 0
  If (IsEmpty(search_birthplace)) Then
     ws_2.Range("E5").Formula = CVErr(xlErrNA)
  Else
     ws_2.Range("E5").Value = search_birthplace
  End If
End Sub

Code Breakdown

  • We declare search_birth_place as the Sub
  • Here we have declared the worksheet variables to store the sheets.
  • We used the If Else statement to execute the code.
  • Inside the VLOOKUP we have provided the author name as lookup_value where the “Details” sheet data was the lookup_arrayand the column_index-number is 2 which will return the values from the Birth Place

  • After that, Save the code >> Run the code.

As a result, we have found the birthplace of the author Arthur Conan Doyle in cell E5.

  • Now if you want to find the result for the second author from our list, you need to modify the code a bit: change the respective cell references.

Hence, you can see the complete Birth Place column.

Use of If..Then..Else Statements for Excel VBA VLOOKUP in Another Worksheet


How to Use VBA VLOOKUP to Get Values from Another Workbook in Excel

Here, we will show how you can use Excel VBA VLOOKUP in another workbook.

In the following dataset, you can see the Information of Author is present in a workbook named  Another Workbook.

On the other hand, we have a workbook named Excel_VBA_VLOOKUP_in _Another_Worksheet. Here, you can see the incomplete Birth Place column.

Next, we will extract the Birth Place of the corresponding Author Name, and we will insert this in the following dataset.

Steps:

  • First of all, to open the VBA Editor window, we will press the ALT+F11 keys.

Here, you can go to the Developer tab >> select Visual Basic to open the VBA Editor window.

At this point, a VBA Editor window will appear.

  • Then, from the Insert tab >> select Module.

  • After that, we will write the following code in the Module.
Sub Lookup_in_another_workbook()
  Dim work_book As Workbook
  Dim work_sheet As Worksheet
  Dim rng_range As Range
  Dim start_name As String
  Set work_book = Workbooks("Another Workbook.xlsx")
  Set work_sheet = work_book.Sheets("Details")
  Set rng_range = work_sheet.Range("B5:C11")
  start_name = Range("B5")
  ActiveCell = Application.WorksheetFunction. _
  VLookup(start_name, rng_range, 2, False)
End Sub

Code Breakdown

  • Here, we declare Lookup_in_another_workbook as the Sub.
  • Here, we have declared the worksheet and workbook variables to store the sheet and workbook name.
  • Within the workbook “Another Workbook”, in the sheet “Details”, using Range(“B5”), we have set the cell where we want to insert the result. Hope you have understood that the result will be derived from the VLOOKUP
  • Inside the VLOOKUP we have provided the author name as lookup_value where the “Details” sheet data was the lookup_arrayand the column_index-number is 2 which will return the values from the Birth Place

  • Afterward, we will Run the code (for your convenience we have highlighted the Run button in the earlier image).

As a result, we have found the birthplace of the author Arthur Conan Doyle in cell E5.

  • Now if you want to find the result for the second author from our list, you need to modify the code a bit: change the respective cell references.

As a result, you can see the complete Birth Place column.


Practice Section

You can download the above Excel file and practice the explained methods.


Conclusion

That’s all for today. We have listed 5 methods to use the Excel VBA VLOOKUP in another worksheet. Hope you will find this helpful. Feel free to comment if anything seems difficult to understand. Let us know which approach you prefer. Don’t hesitate to notify any methods which we might have missed here. You can visit our website Exceldemy for more related articles.


Further Readings

Shakil Ahmed

Shakil Ahmed

My name’s Shakil. My articles are targeted to support you in enriching knowledge regarding different features related to Microsoft Excel. I am fond of literature, hope some knowledge from them will help me providing you some engaging articles even though some weary technical terms.

6 Comments
  1. i want to apply this for visible cells only. I mean i already have some results wants to filer out & then apply this code VlookupFull only for visible cells only. Pls let me know the changes i can make to accomplish this

    • Reply Avatar photo
      Naimul Hasan Arif Aug 14, 2022 at 5:30 PM

      You can try using SpecialCells(xlCellTypeVisible) property while setting the detailsLastRow object. SpecialCells(xlCellTypeVisible) will trigger Excel to consider visible cells only. I hope this edited VBA will help you to get your desired output.

  2. I am having trouble with the following vlookup statement:

    Debug.Print ActiveWorkbook.Name
    Debug.Print ActiveSheet.Name
    Range(“B2:B” & FinalRow).FormulaR1C1 = _
    “=VLOOKUP(RC[1],wsx!R2C2:R9999C3,2,FALSE)”

    First: I verify that the “Range(“B2:B” & FinalRow).FormulaR1C1″, i.e. B2:B is the correct column where I want the vlookup formula is to be plaed.

    Secord: The “Debug.Print” verifies that the ActiveWorkbook and worksheet are correct.

    Finally: What happens when this code line executes is that a file explorer dialog opens with title “Update Values wsx” (wsx is the variable name for the remote worksheet where the lookup values are stored) with the Filename box looking for a file to save the formula? The lookup formula is not stored and the script ends.

    • Hi MRBRAT2U,
      Thanks for reaching us. You can use the following code to execute your desired operation.
      According to your requirement, I have created a source worksheet wsx containing a list of projects with their costs up to 9999 rows.
      1
      For the results, we have enlisted some project names in the Sheet2 column and after running the code we will extract the cost values in the Cost column.
      2
      • Type the following code in your created module.

      Sub vlookupvba()
      Dim FinalRow As Long
      Dim ws As Worksheet
      Set ws = Worksheets("Sheet2")
      FinalRow = ws.Range("A" & ws.Rows.Count).End(xlUp).Row
      Debug.Print ActiveWorkbook.Name
      Debug.Print ActiveSheet.Name
      Range("B2:B" & FinalRow).FormulaR1C1 = "=VLOOKUP(RC[-1],wsx!R2C1:R9999C2,2,FALSE)"
      End Sub

      3
      • Press F5.
      Afterward, you will have the cost values extracted in the Cost column.
      4

  3. Good afternoon,
    I begin to learn VBA for my work, Can you help me problem as my attached code, in vlookup function, variable “Filename1″ not active, inspite of VBA not warning error, Thanks you very much!

    sub tham_dinh ()
    Dim work_sheet As Sheets
    Dim selectFiles As Variant
    Dim iFileNum, iSheetNum, i As Integer
    Dim iLastRowInput, iLastRowOutput As Long
    Dim tieude As Integer
    Dim Filename, Filename1, Filename2 As String

    Application.DisplayAlerts = False
    Application.ScreenUpdating = False

    ‘ Buoc 2:Goi phuong thuc mo nhieu file
    selectFiles = Application.GetOpenFilename(filefilter:=”Excel File (*.xls*),*.xlsx*”, MultiSelect:=True)

    ‘ Buoc 3:Bien chay de mo tung file
    For iFileNum = 1 To 2
    Set wbInput = Workbooks.Open(selectFiles(iFileNum))

    ‘ Buoc 3: lay ten file
    If iFileNum = 1 Then
    Filename1 = wbInput.Name
    End If
    If iFileNum = 2 Then
    Filename2 = wbInput.Name
    End If

    Next

    ‘ VLOOKUP COT T
    Windows(Filename2).Activate
    ‘ Windows(Filename1).Activate
    Set work_book = Workbooks(Filename1)
    Range(“T3”).Select
    ActiveCell.FormulaR1C1 = _
    “=VLOOKUP(RC[-19],'[Filename1]Sheet1’!R3C1:R800C15,13,FALSE)”

    • Hello HUYNH LE TUAN,
      Thank you for your question. I couldn’t understand your question properly. Could you please provide your Excel workbook? It would be easy for me to solve the code error if you send me a copy of your Excel workbook. You can send your workbook here- [email protected]

Leave a reply

ExcelDemy
Logo