Excel’s VLOOKUP function is a very convenient tool for searching specific values and retrieving related data. However, by using VBA (Visual Basic for Applications), you can perform even more flexible and automated data searches. This article provides a detailed explanation of how to implement VLOOKUP-like operations using VBA.
Basic Structure of VLOOKUP Function
First, let’s review Excel’s VLOOKUP function.
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
For example, to search for a value in cell A1 within the range B1:C10 and return the value from the 2nd column (column C) of that row when found:
=VLOOKUP(A1, B1:C10, 2, FALSE)
Using VLOOKUP in VBA
All Excel functions can be used in VBA, so you can call the VLOOKUP function running in Excel from VBA.
Application.WorksheetFunction.VLookup(lookup_value, table_array, col_index_num, False)
By calling the Vlookup function defined in the WorksheetFunction class, you can use the VLOOKUP function from VBA.
However, WorksheetFunction.VLookup raises a run-time error (1004) when the lookup value is not found. To receive the error as a value instead, use Application.VLookup and check the result with IsError.
Sub SafeVlookup()
Dim result As Variant
result = Application.VLookup("lookup", Sheets("Sheet1").Range("B1:C10"), 2, False)
If IsError(result) Then
MsgBox "Not found."
Else
MsgBox result
End If
End Sub
If this is enough for your needs, you do not need a custom function. When you want more flexible data searches, creating a custom function in VBA is useful. The next section introduces a custom function with functionality similar to VLOOKUP.
Code to Recreate VLOOKUP in VBA
Next, let me show you how to perform the same operation in VBA.
Function CustomVlookup(lookup_value As Variant, table_array As Range, col_index_num As Integer) As Variant
Dim cell As Range
For Each cell In table_array.Columns(1).Cells
If cell.Value = lookup_value Then
CustomVlookup = cell.Offset(0, col_index_num - 1).Value
Exit Function
End If
Next cell
CustomVlookup = "Not Found"
End Function
The code above defines a custom function CustomVlookup in VBA that has similar functionality to the VLOOKUP function. Here’s how to use it:
Sub TestCustomVlookup()
Dim result As Variant
result = CustomVlookup("SearchValue", Sheets("Sheet1").Range("B1:C10"), 2)
MsgBox result
End Sub
Advanced Techniques
Up to this point Application.VLookup is enough, but VBA enables techniques like the following.
Partial Match Search
While standard VLOOKUP only supports exact matches, VBA allows partial match searches.
Function CustomVlookupPartial(lookup_value As String, table_array As Range, col_index_num As Integer) As Variant
Dim cell As Range
For Each cell In table_array.Columns(1).Cells
If InStr(1, cell.Value, lookup_value) > 0 Then
CustomVlookupPartial = cell.Offset(0, col_index_num - 1).Value
Exit Function
End If
Next cell
CustomVlookupPartial = "Not Found"
End Function
Switching Between Exact and Partial Matches
It’s also important to be able to switch between exact and partial matches as needed. For example, you can let the user specify this with an argument.
Function CustomVlookupFlexible(lookup_value As Variant, table_array As Range, col_index_num As Integer, Optional partial_match As Boolean = False) As Variant
Dim cell As Range
For Each cell In table_array.Columns(1).Cells
If (partial_match And InStr(1, cell.Value, lookup_value) > 0) Or (Not partial_match And cell.Value = lookup_value) Then
CustomVlookupFlexible = cell.Offset(0, col_index_num - 1).Value
Exit Function
End If
Next cell
CustomVlookupFlexible = "Not Found"
End Function
Error Handling
Run-time errors can still occur, for example when Offset points outside the sheet or when a cell holds an error value such as #N/A. This example catches them with On Error.
Function CustomVlookupWithErrorHandling(lookup_value As Variant, table_array As Range, col_index_num As Integer) As Variant
On Error GoTo ErrorHandler
Dim cell As Range
For Each cell In table_array.Columns(1).Cells
If cell.Value = lookup_value Then
CustomVlookupWithErrorHandling = cell.Offset(0, col_index_num - 1).Value
Exit Function
End If
Next cell
CustomVlookupWithErrorHandling = "Not Found"
Exit Function
ErrorHandler:
CustomVlookupWithErrorHandling = "Error: " & Err.Description
End Function
Practical Usage Examples
Database Search
For example, searching for employee information based on employee ID.
Sub SearchEmployeeInfo()
Dim employeeID As String
Dim result As Variant
employeeID = InputBox("Enter_employee_ID:")
result = CustomVlookup(employeeID, Sheets("EmployeeData").Range("A1:D100"), 2)
If result = "Not Found" Then
MsgBox "Employee_information_not_found."
Else
MsgBox "Employee_Name: " & result
End If
End Sub
Auto-Filling Product Information
Automatically filling cells with information when a product code is entered.
Sub AutoFillProductInfo()
Dim productCode As String
Dim productName As Variant
Dim productPrice As Variant
productCode = Cells(2, 1).Value '_Assumes_product_code_is_in_cell_A2
productName = CustomVlookup(productCode, Sheets("ProductData").Range("A1:C100"), 2)
productPrice = CustomVlookup(productCode, Sheets("ProductData").Range("A1:C100"), 3)
If productName = "Not Found" Or productPrice = "Not Found" Then
MsgBox "Product_information_not_found."
Else
Cells(2, 2).Value = productName '_Enter_product_name_in_cell_B2
Cells(2, 3).Value = productPrice '_Enter_price_in_cell_C2
End If
End Sub
Fast Search with the Find Method
Looping through cells one by one becomes slow with large data sets. The Range.Find method uses Excel’s search feature to locate a matching cell efficiently.
Function FindLookup(lookup_value As Variant, table_array As Range, col_index_num As Integer) As Variant
Dim found As Range
Set found = table_array.Columns(1).Find(What:=lookup_value, LookAt:=xlWhole, LookIn:=xlValues)
If found Is Nothing Then
FindLookup = "Not Found"
Else
FindLookup = found.Offset(0, col_index_num - 1).Value
End If
End Function
Find remembers the LookAt and LookIn settings from the previous search, so always specify them explicitly.
チェック
Newer versions of Excel, such as Microsoft 365, provide the XLOOKUP function, and some versions expose it to VBA as WorksheetFunction.XLookup. Check that it is available in your environment.
Summary
Using VBA enables flexible data searches that aren’t limited by Excel’s VLOOKUP function. By creating custom functions combining these techniques, you can easily perform processing tailored to your business needs. Please try incorporating these into your own projects.