Use Excel VBA VLOOKUP to Find Values in Another Worksheet

In this article, we will demonstrate how to use the Excel VBA VLOOKUP in another worksheet.

Suppose we have the dataset below, stored in the sheet called “Data”.

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

We will execute the VLOOKUP operation between the sheets using VBA code. We used Excel 365, but you can use any available Excel version.

Example 1 – Using VLOOKUP for Static Changes in Another Worksheet

To start with, let’s use VBA VLOOKUP in another worksheet in such a way that every execution will require changing manually.

We have introduced a Birth Place column to the author lists dataset. Let’s find the birthplace of each writer that is listed in the “Details” sheet.

Note that the current sheet’s name is “Static Changes”. We copied the data from the “Data” sheet to here.

Steps:

• To open the VBA Editor window, press the ALT+F11 keys.

Or, go to the Developer tab >> select Visual Basic.

A VBA Editor window will appear.

• From the Insert tab >> select Module.

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

• Run the code.

As a result, the birthplace of the author Arthur Conan Doyle is returned in cell E5.

• To find the result for the second author from our list, modify the code to change the respective cell references.

After manually finding the result for each cell, the complete Birth Place column looks as follows:

Example 2 – Using VLOOKUP to Change Selection of Another Worksheet

In the previous method, we applied VBA VLOOKUP in another worksheet for static changes. Now let’s use VLOOKUP for a selected cell. For this example, we have copied the data to the “Change Selection” sheet.

We will select a cell from the Birth Place column and our code will derive the birthplace of the author from that row.

In order to execute the code, we will need not just a cell selection, but a button or shortcut key too. We’ll use a shortcut key here.

Steps:

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

The Record Macro dialog box will open.

• Set the Macro name as Vlookup_shortcut.
• Set CTRL+SHIFT+J in the Shortcut key box.

Or, set the Macro name and Shortcut key as you like.

• Follow the Steps described in Example 1 to bring up the VBA Editor window.
• Open the module for the Vlookup_shortcut macro and enter the following code:

Sub Vlookup_shortcut()
'
' Keyboard Shortcut: Ctrl+Shift+J
'

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

• Save the code and go back to the Worksheet.
• Select cell E5.
• Press the shortcut key CTRL+SHIFT+J.

The author’s Birth Place is returned. The formula can be seen in the Formula Bar.

• Drag the formula down with the Fill Handle tool to get the results for the rest of the cells.

The complete Birth Place column looks as follows:

Read More: Excel VBA to Vlookup in Another Workbook Without Opening

Example 3 – Using VLOOKUP to Find All Values from Another Worksheet

Now we’ll use VBA code to lookup an entire dataset at once. We’ve copied the dataset to the “Entire At Once” sheet for this example.

Steps:

• Follow the Steps in Example 1 to bring up the VBA Editor window.
• Enter the following code in the module:

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

• Save the code and Run it.

The Birth Places of all the authors are returned at once.

Example 4 – Using VLOOKUP to Get Values for a Particular Sheet

In this method, we will use the VBA VLOOKUP for a particular worksheet. We have copied the dataset to a sheet named “Definite Sheet”.

Steps:

• Follow the Steps described in Example 1 to bring up the VBA Editor window.
• Enter the following code in the module:

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

• Save the code and Run it.

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.

The birthplace of the author Arthur Conan Doyle is returned in cell E5.

• To find the result for the second author in the list, modify the code to change the respective cell references.

The complete Birth Place column looks as follows:

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

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

We can also use the If..Then..Else statement to perform VBA VLOOKUP in another worksheet. We have copied the dataset to the “IF ELSE Statement” sheet.

Steps:

• Follow the Steps described in Method1 to bring up the VBA editor window.
• Enter the following code in the window:

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

• Save the code and Run it.

We have found the birthplace of the author Arthur Conan Doyle in cell E5.

• To find the result for the second author in the list, modify the code to change the respective cell references.

The complete Birth Place column looks as follows:

How to Use VBA VLOOKUP to Get Values from Another Workbook

Finally, let’s use Excel VBA VLOOKUP in another workbook entirely.

In the following dataset, the Author’s Information is present in a workbook named Another Workbook.

We also have a workbook named Excel_VBA_VLOOKUP_in _Another_Worksheet, which has an incomplete Birth Place column.

We will extract the Birth Place of the corresponding Author Name, and insert this in the following dataset:

Steps:

• To open the VBA Editor window, press the ALT+F11 keys.

Or go to the Developer tab >> select Visual Basic.

A VBA Editor window will appear.

• From the Insert tab, select Module.

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

• Run the code.

We have found the birthplace of the author Arthur Conan Doyle in cell E5.

• To find the result for the second author on the list, modify the code to change the respective cell references.

The complete Birth Place column looks as follows:

Read More: Excel VBA to Vlookup Values for Multiple Matches

Download Workbook

Further Reading

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