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

Code Breakdown

We declare Vlookup_Static_Change as the Sub.
Within this sheet named Static Changes, in Range(“E5”) we want to place the result, which is derived from the VLOOKUP formula.
Inside the VLOOKUP we have provided the author name as lookup_value, the “Details” sheet data as the lookup_array and the column_index-number as 2 (which will return the values from the Birth Place column).

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

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 selected. R and C represent row and column.
Our lookup_value is 3 columns to the left of the selected cell, hence the syntax RC[-3].
The lookup_array cell reference is B5:C15, which means from row 5 column 2 to row 11 column 3, so we have set R5C2:R11C3 (in the “Details” sheet).

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:

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

Code Breakdown

We declare the worksheet variables to store the sheets, then authorsLastRow and detailsLastRow to get the number of the last row of both sheets, in order to make the list dynamic.
When defining these two variables, we set the range to start from cell B5 and keep counting to the end (until a value is inserted).
The dataRng variable sets the lookup_array.
Then a FOR loop goes through the cells and derives the values.

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

Code Breakdown

We declare Lookup_Birth_Place as the Sub.
From the “Details” sheet, it will look up the values for the “Definite Sheet”.
We use Range(“E5”), to set the cell in which to insert the result, which is derived from the VLOOKUP.
Inside the VLOOKUP, the author name is the lookup_value, the “Details” sheet data is the lookup_array and the column_index-number is 2 (which will return the values from the Birth Place column).

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:

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

Code Breakdown

We declare search_birth_place as the Sub.
We declare the worksheet variables to store the sheets.
We use an If Else statement to execute the code.
Inside the VLOOKUP we provide the author name as the lookup_value, the “Details” sheet data as the lookup_array and the column_index-number as 2 (which will return the values from the Birth Place column).

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

Code Breakdown

We declare Lookup_in_another_workbook as the Sub.
We declare 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 set the cell where we want to insert the result, which is derived from the VLOOKUP.
Inside the VLOOKUP we provide the author name as lookup_value, the “Details” sheet data as the lookup_array and the column_index-number as 2 (which will return the values from the Birth Place column).