VLookup or Vertical Lookup is Microsoft’s one of the best lookup functions. It is used to search for a specific value in the leftmost column of a table (known as the lookup table) and retrieve a corresponding value from a column to the right.
No doubt, the VLookup function has a few drawbacks. However, those drawbacks can be omitted using Excel’s powerful VBA tool.
By integrating the two systems together, we can automate complex data analysis, perform data cleansing and transformation, and generate customized reports in a flash.
In this post, we will teach you how to use Vlookup in VBA, and some tips to omit errors.
Integration of VLOOKUP To VBA
We all know the purpose of the VLOOKUP function. The goal is to find and extract a single piece of information across each column.
For example, if you have a list of employee IDs and want to retrieve their corresponding names or departments from a separate table, VLOOKUP can help you fetch that data efficiently.
But, here’s the catch.
There are four things to keep in mind while using this function:
- VLOOKUP can only perform left-to-right lookups, meaning it can only search for values in the leftmost column of the lookup range and retrieve values from columns to the right. That said, your lookup value should always be in the leftmost column.
- VLOOKUP performs approximate matching when the fourth argument (range_lookup) is set to True or 1. It searches for the closest match rather than an exact match. This is useful in some cases, but it can also lead to unexpected results. To ensure an exact match, the range_lookup must be explicitly set to False or 0.
- The most important parameter of VLOOKUP is that the data in the lookup range should be sorted in ascending order based on the lookup values. This is because VLOOKUP performs an approximate match by default, meaning it looks for the closest match that is less than or equal to the lookup value.
- The column index argument should be specified as a number.
VLOOKUP Syntax in VBA
Normally, the syntax of VLOOKUP function is:
VLookup(lookup_value, table_array, col_index_num, [range_lookup])
There are four arguments available here. Detailed information on these 4 arguments are mentioned below:
| Arguments | Description | Required/Optional Field |
| lookup_value | The value you are searching in the first column of the datasheet. | Required |
| table_array | It is the range in which the function will search the lookup value and its corresponding result. | Required |
| col_index_num | It is the column number within the table_array range. For Example: 2 will denote the second column. | Required |
| [range_lookup] | This argument is dedicated to finding the approximate or partial match. By default, the value is set to TRUE, meaning the function will find an approximate match. There are two options-
TRUE = approximate match |
Optional |
Now, these arguments are the basic parameters one should follow while using the Vertical LOOKUP Function. But, within Excel’s VB programming language, we have to add some variables.
Using VBA variables with VLOOKUP
While using the Visual basic Editor, we need to assign some subroutines for the code to run properly. Let’s look at an example:
Dim lookupValue As Variant
Dim tableArray As Range
Dim columnIndex As Long
Dim rangeLookup As Boolean
Dim result As Variant
lookupValue = "John"
Set tableArray = Range("A1:B10")
columnIndex = 2
rangeLookup = False
result = Application.WorksheetFunction.VLookup(lookupValue, tableArray, columnIndex, rangeLookup)
Handling optional parameters in VBA:
In the first section of the code, we used several variables using the Dim statement. Mainly, the variables are used to gather and store the data.
Let’s break it down a little:
| Statement | Description |
| Dim lookupValue As Variant | The lookupvalue variable is set to a Type Variant to store the value you want to lookup. |
| Dim tableArray As Range | The tableArray variable is set to Range to store the range of cells that contain the data you want to search. |
| Dim columnIndex As Long | The columnIndex variable is set to Long to store the column number from which you want to retrieve the result. |
| Dim rangeLookup As Boolean | The variable rangeLookup is set to Boolean to determine whether you want an exact match (False) or an approximate match (True). |
| Dim result As Variant | The result variable is set to variant to store the final outcome of the VLOOKUP operation. |
The next section contains all the four arguments of the VLOOKUP function. We have to set the lookup value and fix the column numbers every time we have to run the code.
Finally, in the third segment, we used the code,
result = Application.WorksheetFunction.VLookup(lookupValue, tableArray, columnIndex, rangeLookup)
Here, the result of the VLOOKUP operation is stored in a result variable. The VLookup function is executed using the WorksheetFunction object from the Application class. This ensures that the VLOOKUP function is treated as a built-in Excel function.
How to Use VLookup in Excel VBA
In this section, we will see 3 different scenarios of using VLOOKUP in VBA with Practical Examples and VBA code. The basic method is the same.
Go to the Developer Tab. Click on Visual Basic. Insert a new module and paste the VBA code depending on the criteria. Save and Run the code using F5.
Now, let’s look at the three different scenarios:
Scenario 1: Use VLOOKUP to Retrieve Data from Another Worksheet
Let’s say we have two worksheets: Raw Data Sheet, and Master Data. I want to perform a VLOOKUP on a “Master Data” sheet to retrieve data from “Raw Data Sheet” based on a lookup value.
The VBA code will be:
Dim lookupValue As Variant
Dim tableArray As Range
Dim columnIndex As Long
Dim rangeLookup As Boolean
Dim result As Variant
lookupValue = "Aria"
Set tableArray = Worksheets("Raw Data").Range("A1:B10")
columnIndex = 2
rangeLookup = False
result = Application.WorksheetFunction.VLookup(lookupValue, tableArray, columnIndex, rangeLookup)
Worksheets("Master Data").Range("A1").Value = result
This code helps a lot if you have multiple worksheets. In this example, we assign the lookup value “Aria” to lookupValue, set the lookup range as “Sheet1” range A1:B10 using tableArray, specify the column index as 2, and set rangeLookup to False for an exact match.
The result will be presented in the Master data sheet upon pressing Enter.
Conditions:
- The lookup value should be in the leftmost column.
- The table array data needs to be sorted in ascending order.
- Lookup range needs to be adjusted every time if the criteria differs.
Scenario 2: Debug VLOOKUP errors in VBA
The four most common errors while using VLOOKUP are #NAME?, #N/A error, #VALUE! Error, and #REF! Error.
#N/A error occurs due to invalid data in the worksheet, invalid VLOOKUP cell reference, incorrect lookup range, incorrect number format, and excess spaces or non-printing characters.
#VALUE! Error occurs due to incorrect column number.
#NAME? Occurs for incorrect function name.
#REF! Error occurs due to invalid references to another table.
You can handle such types of errors with VBA.
VBA Code for Error Handling:
Dim lookupValue As Variant
Dim tableArray As Range
Dim columnIndex As Long
Dim rangeLookup As Boolean
Dim result As Variant
lookupValue = "Aria"
Set tableArray = Range("A1:B10")
columnIndex = 2
rangeLookup = False
On Error Resume Next
result = Application.WorksheetFunction.VLookup(lookupValue, tableArray, columnIndex, rangeLookup)
On Error GoTo 0
If IsError(result) Then
MsgBox "Lookup value not found."
Else
Range("C1").Value = result
End If
Explanation:
In this example, we used error handling techniques to check if an error occurred. We used the variable On Error Resume Next to bypass the error and assign the result to the Master Data Sheet. Then, we used On Error GoTo 0 to turn off the error handling.
To check if the result is an error, we used the ISERROR Function, IsError(result). If it is an error, then Excel will display a message box saying “Lookup value not found.” If it is not an error, the result is cell C1 (Range(“C1”).Value).
Scenario 3: Apply The VLOOKUP Function with Variables and Dynamic Ranges
If you have a dynamic range for the lookup data and want to use variables for the lookup value and the column index, then this VBA Code is for you.
VBA Code:
Dim lookupValue As Variant
Dim tableArray As Range
Dim columnIndex As Long
Dim rangeLookup As Boolean
Dim result As Variant
lookupValue = Range("D1").Value
Set tableArray = Range("A1:B" & Range("B1").Value)
columnIndex = Range("C1").Value
rangeLookup = False
result = Application.WorksheetFunction.VLookup(lookupValue, tableArray, columnIndex, rangeLookup)
Range("E1").Value = result
Explanation:
This code snippet dynamically performs a VLOOKUP operation using values from specific cells, allowing for flexibility in the lookup value, the lookup range, the column index, and the ability to handle an exact match requirement.
Frequently Asked Questions
Question: Can you use VLOOKUP in VBA?
Answer: Yes, you can use the VLOOKUP Function in VBA. But the default syntax =VLOOKUP( array won’t work. For adding the Vertical lookup function in VBA, you have to set some variables and then use the arguments to complete the code.
Question: Can VLOOKUP be used in macro?
Answer: Vloookup can be used in macro. However it’s a very uncommon case. Normally, we use Vlookup with VBA codes and assign a button to automate our task.
Question: What VBA code is similar to VLOOKUP?
Answer: There are three similar VLOOKUP functions. One is the HLOOKUP (Horizontal Lookup), other is the XLOOKUP, and the last one is the INDEX and MATCH function. INDEX-MATCH and XLOOKUP are the most versatile functions to use in a VBA code.
Conclusion
In summarizing the whole context, we have covered several aspects of using VLOOKUP in VBA.
Mostly, while using VLOOKUP you have to deal with column index number, exact and appropriate match.
Apart from Vertical Lookups or HLOOKUP, there are alternative lookup methods. We like the INDEX/MATCH function for its versatility and logical improvements.
