VBA Code to Apply Vlookup in Excel (5 Examples)

by

What is Vlookup

VLOOKUP is a popular function in Microsoft Excel used to search for a specific value in the first column of a range (also known as the lookup table) and retrieve a corresponding value from a different column within that range. The term “VLOOKUP” stands for “Vertical Lookup” because it searches vertically within a column.

The syntax for the VLOOKUP function is as follows:

VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Here’s a breakdown of the function’s arguments:

  • lookup_value: The value you want to find in the first column of the lookup table.
  • unprotect sheet: The range of cells that makes up the lookup table. It should include the column containing the lookup value and the column from which you want to retrieve the result.
  • col_index_num: The number of the column within the lookup table from which you want to retrieve the result. The first column is 1, the second column is 2, and so on.
  • range_lookup: An optional argument that specifies whether you want an exact match (FALSE or 0) or an approximate match (TRUE or 1). If omitted, the default value is TRUE or 1.

When you use the VLOOKUP function, Excel searches for the lookup value in the first column of the table array. Once it finds a match, it returns the value from the corresponding column specified by the col_index_num argument.

VLOOKUP is commonly used for tasks such as retrieving data from a separate table, performing data analysis, merging datasets, and creating reports in Excel.

1 VBA Vlookup for Vlookup

Here’s an example of VBA code for performing a VLOOKUP in Excel

Sub VlookupExample()
    Dim lookupValue As Variant
    Dim lookupRange As Range
    Dim resultRange As Range
    Dim result As Variant
    
    ' Set the lookup value
    lookupValue = "Value to Lookup"
    
    ' Set the range to perform the lookup
    Set lookupRange = Sheets("Sheet1").Range("A2:B10")
    
    ' Set the range where the result will be returned
    Set resultRange = Sheets("Sheet1").Range("D2:D10")
    
    ' Perform the VLOOKUP
    result = Application.WorksheetFunction.VLookup(lookupValue, lookupRange, resultRange.Column - lookupRange.Column + 1, False)
    
    ' Check if the result is found
    If Not IsError(result) Then
        ' Display the result
        MsgBox "Result: " & result
    Else
        ' Display an error message
        MsgBox "Value not found!"
    End If
End Sub

In this example, the VLOOKUP is performed on “Sheet1” in the range “A2:B10” with the lookup value “Value to Lookup”. The result is returned in the range “D2:D10”. The code uses the VLookup function from the WorksheetFunction class in VBA.

You can modify the ranges and values as per your specific requirements. Also, make sure to update the sheet names and range addresses accordingly in the code.

When you run this code, it will display a message box with the result if the lookup is successful. Otherwise, it will display an error message.

2 VBA Vlookup from Another Worksheet

Here’s an example of VBA code to perform a VLOOKUP from another worksheet in Excel:

Sub VlookupFromAnotherWorksheet()
    Dim lookupValue As Variant
    Dim lookupRange As Range
    Dim resultRange As Range
    Dim result As Variant
    Dim wsLookup As Worksheet
    
    ' Set the lookup value
    lookupValue = "Value to Lookup"
    
    ' Set the worksheet to perform the lookup
    Set wsLookup = Worksheets("Sheet1") ' Replace "Sheet1" with the desired worksheet name
    
    ' Set the range to perform the lookup
    Set lookupRange = wsLookup.Range("A2:B10")
    
    ' Set the range where the result will be returned
    Set resultRange = wsLookup.Range("D2:D10")
    
    ' Perform the VLOOKUP
    result = Application.WorksheetFunction.VLookup(lookupValue, lookupRange, resultRange.Column - lookupRange.Column + 1, False)
    
    ' Check if the result is found
    If Not IsError(result) Then
        ' Display the result
        MsgBox "Result: " & result
    Else
        ' Display an error message
        MsgBox "Value not found!"
    End If
End Sub

In this example, the VLOOKUP is performed on a specific worksheet named “Sheet1”. You can modify the worksheet name to match the actual worksheet you want to perform the lookup on. The code uses the Worksheets collection to reference the desired worksheet.

Make sure to update the ranges, values, and sheet names as per your specific requirements. The code will display a message box with the result if the lookup is successful. If the value is not found, it will display an error message.

3 VBA Code for Vlookup with Dynamic Range

Here’s an example of VBA code for performing a VLOOKUP with a dynamic range in Excel

Sub VlookupWithDynamicRange()
    Dim lookupValue As Variant
    Dim lookupRange As Range
    Dim resultRange As Range
    Dim result As Variant
    Dim lastRow As Long
    Dim ws As Worksheet
    
    ' Set the lookup value
    lookupValue = "Value to Lookup"
    
    ' Set the worksheet to perform the lookup
    Set ws = Worksheets("Sheet1") ' Replace "Sheet1" with the desired worksheet name
    
    ' Find the last row in the lookup range (assuming data starts from column A)
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    
    ' Set the range to perform the lookup
    Set lookupRange = ws.Range("A2:B" & lastRow)
    
    ' Set the range where the result will be returned
    Set resultRange = ws.Range("D2:D" & lastRow)
    
    ' Perform the VLOOKUP
    result = Application.WorksheetFunction.VLookup(lookupValue, lookupRange, resultRange.Column - lookupRange.Column + 1, False)
    
    ' Check if the result is found
    If Not IsError(result) Then
        ' Display the result
        MsgBox "Result: " & result
    Else
        ' Display an error message
        MsgBox "Value not found!"
    End If
End Sub

In this example, the VLOOKUP is performed on a specific worksheet named “Sheet1”. You can modify the worksheet name to match the actual worksheet you want to perform the lookup on. The code dynamically determines the last row of data in column A using the End(xlUp) method.

The code sets the lookup range from “A2” to the last row of data and the result range from “D2” to the last row of data. This ensures that the VLOOKUP operates on the entire range of data, even if the size of the range changes.

Make sure to update the ranges, values, and sheet names as per your specific requirements. The code will display a message box with the result if the lookup is successful. If the value is not found, it will display an error message.

4 Excel VBA Vlookup in another workbook without Opening

Performing a VLOOKUP in another workbook without opening it requires a different approach. You can use the VLOOKUP function in combination with the ExecuteExcel4Macro method. Here’s an example of VBA code that demonstrates how to achieve this

Sub VlookupInAnotherWorkbook()
    Dim lookupValue As Variant
    Dim lookupRange As Range
    Dim result As Variant
    Dim workbookPath As String
    Dim workbookName As String
    Dim formula As String
    
    ' Set the lookup value
    lookupValue = "Value to Lookup"
    
    ' Set the path and name of the workbook
    workbookPath = "C:\Path\To\Workbook.xlsx" ' Replace with the actual path
    workbookName = "Workbook.xlsx" ' Replace with the actual workbook name
    
    ' Set the range to perform the lookup
    Set lookupRange = Nothing ' Update this to refer to the actual range in the other workbook
    
    ' Construct the VLOOKUP formula
    formula = "=VLOOKUP(" & lookupValue & ",'" & workbookPath & "\[" & workbookName & "]Sheet1'!$A$1:$B$10,2,FALSE)"
    
    ' Perform the VLOOKUP using ExecuteExcel4Macro
    result = Application.ExecuteExcel4Macro(formula)
    
    ' Check if the result is found
    If Not IsError(result) Then
        ' Display the result
        MsgBox "Result: " & result
    Else
        ' Display an error message
        MsgBox "Value not found!"
    End If
End Sub

In this code, you need to specify the path and name of the workbook you want to perform the VLOOKUP on. Update the workbookPath and workbookName variables to match the actual file path and workbook name.

Additionally, you need to update the lookupRange variable to refer to the actual range in the other workbook.

For example, if the range is “Sheet1!A1:B10”, you would set lookupRange to Worksheets(“Sheet1”).Range(“A1:B10”) in the other workbook.

The code constructs a VLOOKUP formula using the workbook path, workbook name, and the range you specified. It then uses the ExecuteExcel4Macro method to execute the formula and retrieve the result.

Please note that the workbook you are performing the VLOOKUP in must be open in order to execute the formula in another workbook without opening it.

5 Excel VBA Vlookup Variable lookup Value

Here’s an example of VBA code that demonstrates how to perform a VLOOKUP with a variable lookup value in Excel

Sub VlookupWithVariableLookupValue()
    Dim lookupValue As Variant
    Dim lookupRange As Range
    Dim resultRange As Range
    Dim result As Variant
    
    ' Set the variable lookup value
    lookupValue = Sheets("Sheet1").Range("A1").Value
    
    ' Set the range to perform the lookup
    Set lookupRange = Sheets("Sheet2").Range("A2:B10")
    
    ' Set the range where the result will be returned
    Set resultRange = Sheets("Sheet2").Range("D2:D10")
    
    ' Perform the VLOOKUP with variable lookup value
    result = Application.WorksheetFunction.VLookup(lookupValue, lookupRange, resultRange.Column - lookupRange.Column + 1, False)
    
    ' Check if the result is found
    If Not IsError(result) Then
        ' Display the result
        MsgBox "Result: " & result
    Else
        ' Display an error message
        MsgBox "Value not found!"
    End If
End Sub

In this example, the VLOOKUP is performed using a variable lookup value. The lookup value is obtained from cell A1 on “Sheet1” using the statement lookupValue = Sheets(“Sheet1”).Range(“A1”).Value. You can modify this statement to retrieve the lookup value from any desired cell or source.

The code sets the lookup range to “A2:B10” on “Sheet2” and the result range to “D2:D10″ on “Sheet2”. Make sure to update the sheet names and ranges according to your specific workbook structure.

The VLOOKUP is performed using the WorksheetFunction.VLookup method, and the result is stored in the result variable. The code then checks if the result is found and displays either the result or an error message using message boxes.

Feel free to modify the ranges, sheet names, and variable lookup value assignment as per your requirements.

Check All VBA Codes

Join Our Telegram Group techguruplus telegram group Join Our WhatsApp Group techguruplus whatsapp group
Nazim Khan - Author Image

Nazim Khan (Author) 📞 +91 9536250020
[MBA in Finance]

Nazim Khan is an expert in Microsoft Excel. He teaches people how to use it better. He has been doing this for more than ten years. He is running this website (TechGuruPlus.com) and a YouTube channel called "Business Excel" since 2016. He shares useful tips from his own experiences to help others improve their Excel skills and careers.

Leave a Comment