Learn about VBA VLookup
In VBA, you can use the VLookup function to search for a value in the first column of a table and return a value in the same row from another column. This can be done using the WorksheetFunction.VLookup method, which essentially replicates Excel’s VLOOKUP function in VBA.
Basic Syntax of VLookup in VBA
lookup_value: The value you want to look up.
table_array: The range where the lookup should occur (e.g., Sheet1.Range(“A1:C10”)).
col_index_num: The column number in table_array from which to return the value.
range_lookup (optional): True for approximate match, False for an exact match. Default is True.
Example 1: Simple VLookup in VBA
This example finds a salary based on Imp ID.
Data :
And then result will be
Example 2. Using VLookup to Populate Data in a Loop
And then result will be
With if Condition:
Example 3: VLookup with Error Handling
Since VLookup will throw an error if the lookup_value is not found, it’s best to add error handling.
Example 4: Using VLookup to Populate Data in a Loop
This example demonstrates how to use VLookup within a loop to search for multiple values and populate them in a new column.
Key Points to Remember
- Error Handling: VLookup will throw an error if the lookup_value is not found. Use On Error Resume Next to handle this gracefully.
- Exact Match: Set range_lookup to False to ensure an exact match; otherwise, it may return approximate matches, which can cause unexpected results.
- Use WorksheetFunction for Excel Functions: To use Excel functions in VBA, prefix them with WorksheetFunction.
Example 5. Using VLookup Across Multiple Sheets
This example demonstrates using VLookup to find a value in a table located on another sheet.
Q and A
Q1. How do I handle errors in VBA VLookup when a value is not found?
Answer: In VBA, if a VLookup function doesn’t find a match, it will throw an error. To handle this, you can use On Error Resume Next before performing the VLookup and then check if the result is an error using IsError. Here’s an example:
Q2. Can I use VLookup to look up values on another worksheet?
Answer: Yes, VLookup can look up values on any worksheet as long as you reference the correct sheet in the range. Specify the sheet in the Range function, like this:
result = Application.WorksheetFunction.VLookup(lookupValue, Sheets(“Sheet2”).Range(“A2:B100”), 2, False)
Q3. How can I perform a case-sensitive VLookup in VBA?
Answer: Unfortunately, VLookup in VBA is not case-sensitive by default. To perform a case-sensitive lookup, you’d need to use a custom loop or a combination of Match and other functions. Here’s an example that loops through cells to find an exact match by case:
Q4. How can I retrieve multiple values with VLookup in VBA?
Answer: If you need to pull data from multiple columns for a single lookup value, perform VLookup multiple times, each with a different column index. Alternatively, you can use a loop to automate this. Here’s an example:
Q5. Is it possible to use a dynamic range for the table array in VLookup?
Answer: Yes, you can use a dynamic range by defining the last row of the data range. Here’s how you can use VBA to define a range that adjusts to the last row:
