How to Reference Another Sheet in VLookup Using VBA

1. What Is the VLookup Function
2. Use VLookup Referencing Another Sheet in VBA
3. Conclusion

VBA is short for Visual Basic for Applications. It is a programming language for all Microsoft Office applications, such as Excel, Word, and PowerPoint.

Using VBA allows the users to optimize their time on the Microsoft Office Application by writing codes, known as Macros. This way, the user can run the Macro for a task to make it automated instead of manually performing the same actions repeatedly, saving time.

VBA provides developers with a variety of different functions and features. In this article, we will be learning about the VLookup function and how it is used, especially when we want to reference another sheet.

What Is the VLookup Function

The VLookup function is used in Excel when we want to find the value of a particular row in a table or range. This is extremely useful when working with a large spreadsheet; manually looking for corresponding values can result in human errors.

The syntax of the VLookup function is as follows:

VLOOKUP(lookup_value, Sheet!range, col_index_num, [range_lookup])

Parameters:

The VLookup function takes four parameters, as shown in the syntax above. These are explained below.

1. lookup_value - This is the cell against which you want to find the value in the row.

2. Sheet!range - Here, you define the range of cells where the lookup value and the return value are located. A general rule is that the lookup value must always be in the first column of the range specified, or the VLookup function would not work correctly.

3. col_index_num - As a rule, the column number specified here must be present in the range given previously. The columns are counted from 1.

   For example, if you have specified C3:F10 as the range, then C is column number 1, D is column number 2, E is column number 3, and so on.

4. range_lookup - The last parameter is optional and allows you to specify whether you want an exact or approximate return value. For an exact value, you specify FALSE or 0; for an approximate value, you write TRUE or 1.

   If this parameter is not specified, the default value is TRUE, and an approximate result is returned.

The syntax and parameters explained above can be best illustrated through an example. Suppose we have the following Excel sheet.

Suppose we want to look up the employee’s name having ID 1004. We use the VLookup function to do this using the following code.

Sub example()
On Error Resume Next
Range("C8") = Application.WorksheetFunction.VLookup(Range("A5"), Range("A2:D6"), 3, False)
End Sub

After running this code in VBA, we get the corresponding name for ID 1004 returned in the cell C8.

Note: The On Error Resume Next line prevents VBA from throwing Error 1004 if the corresponding value is not found by resuming the code from the next line.

Now that you have learned about the VLookup function in VBA let us explain how we can use it to reference cells from another sheet.

Use VLookup Referencing Another Sheet in VBA

Suppose we want to look up a value for a corresponding cell, but the range where the value resides is in another sheet.

How do we reference another sheet in such a case? Let us learn that through an example.

Adding to the same example above, suppose we have another sheet with only the employee IDs and their contact information, as shown below.

As we can see here, the last cell of the Contact column is empty, meaning that we do not have the contact information for the employee having ID 1005.

The task now is to look up the Contact value from Sheet1 corresponding to the ID 1005 and write it in the cell B6 in Sheet2 against the ID 1005 as required. To do this, we can easily use the VLookup function with a few changes.

The main adjustment we will have to do in the parameters of the VLookup function is to specify the sheet whose cells we are referring to in the range; otherwise, there will be ambiguity which might produce erroneous results.

For this purpose, one solution is to define the sheet names as Worksheet variables and use them to refer to the appropriate cells. The VBA code for this is as follows:

Sub example2()
Dim ws1 As Worksheet, ws2 As Worksheet
Set ws1 = Worksheets("Sheet1")
Set ws2 = Worksheets("Sheet2")
On Error Resume Next
ws2.Range("B6") = Application.WorksheetFunction.VLookup(ws2.Range("A6"), ws1.Range("A2:D6"), 4, False)
End Sub

Here, we can see that every time a range is mentioned, we specify which sheet it belongs to avoid ambiguity. After running this VBA code, the state of Sheet2 will be as follows, indicating that our code is running correctly.

However, if you do not want to declare separate variables, you can directly reference the sheet name as a Worksheet. The following code works for this and generates the same output.

Sub example()
On Error Resume Next
Worksheets("Sheet2").Range("B6") = Application.WorksheetFunction.VLookup(Worksheets("Sheet2").Range("A6"), Worksheets("Sheet1").Range("A2:D6"), 4, False)
End Sub

Conclusion

This sums up our discussion on the VLookup function in VBA, a useful programming language that saves valuable time for Microsoft Office users.

We hope you have learned how to use the VLookup function in VBA to reference cells in other sheets besides the active one.