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.

Excel VBA VLOOKUP in Another Worksheet: 5 Methods

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”.

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.

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.

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 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

• 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.

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.

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.

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

• 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: Excel VBA to Vlookup in Another Workbook Without Opening

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.

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

• 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.

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”.

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

• 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.

Read More: How to Use Excel VBA VLookup with Named Range

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”.

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

• 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.

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 the 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

• 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.

Read More: Excel VBA to Vlookup Values for Multiple Matches

Practice Section

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

Download Workbook

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

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 that we might have missed here.

Further Readings

• Excel VBA Vlookup with Multiple Criteria
• How to Use Excel VBA VLookup Within Loop
• Excel VBA: Working with If, IsError, and VLookup Together