In this post, you will learn about VLookup in Excel and how you can use it in your Excel spreadsheet using Excel VBA.
VLOOKUP Function in Excel VBA
The VLOOKUP function in VBA is not a built-in function, so it needs to be called using the worksheet. It is used to search a value in an array or a table and return its corresponding value from another column. It is more like the dictionary’s key and pair combination.
The key value must be present in the first column of search. You can command excel to look for an exact match or a similar match.
Syntax for VLookup in Excel VBA
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
In VBA , the VLOOKUP function can be used as:
Application.WorksheetFunction.vlookup(lookup_value, table_array, col_index_num, range_lookup)
When to use the VLOOKUP Function in Excel?
VLookup is the best-tailored function to look for exact matching data or a similar data in a column.
If the matching data point is found, the function goes to the exact data point in that row and fetches the value from the cell which is a specified number of columns to the right.
Sample program using VLOOKUP in Excel VBA
First create a table with the data from the below image.
Enter the following code in the Visual basics tab under the Developer option.
Code:
Sub UsingVlookup()
Dim product_id As Long
Dim price As Long
product_id = 10101
Set within = Range("B4:D13")
selling_price = Application.WorksheetFunction.VLookup(product_id, within, 3, False)
Result = "Selling price of the product is: " & selling_price
MsgBox Result
End Sub
