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 Plac*e *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:**

*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
**Inser**t 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.

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

**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: **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
```

**Code Breakdown**

- Here we have declared the worksheet variables to store the sheets. Then
**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.

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

**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 use
**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.

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

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

## 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
**Inser**t 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.

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

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

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.

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

wsxcontaining a list of projects with their costs up to9999rows.For the results, we have enlisted some project names in the

Sheet2column and after running the code we will extract the cost values in theCostcolumn.• Type the following code in your created module.

• Press

F5.Afterward, you will have the cost values extracted in the

Costcolumn.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]