VBA LOOKUP function: Explained with 5 Examples
VLOOKUP function is used to find things in an Excel sheet (table or range) by row. Basically, VLOOKUP is a sheet function but you may use it in VBA as well.
Using VLOOKUP in VBA
VLOOKUP function is used to find things in an Excel sheet (table or range) by row.
Basically, VLOOKUP is a sheet function but you may use it in VBA as well.
In this tutorial, we will show you examples of using VLookup in VBA.
How to use VLookup function in VBA?
For using Excel sheet functions, you may use the Application.WorksheetFunction property as shown in the examples below.
An example to find Product Price using VLookup
Consider this Excel sheet for our demo:
We will find the price of “Rice” by using VLookup function as follows:
The code:
Sub vlookup_ex()
Price = Application.VLookup("Rice", Range("B2:D10"), 2, False)
MsgBox "Price of Rice = " & Price
End Sub
Result:
How did it work?
- We searched for “Rice” in the range
- Then we provided a range from B2 (Product Name column) to D8 (Status) column in our sheet
- For returned information – we specified column 2 in our range. This is the Price column in our sheet, as want to get the price of the given product.
- False tells to match the exact given term.
Change the Interior color of the whole row which is found by VLookup
In the following example, we will search for the given term using VLookup and if found, we will change the font color of the whole row.
Code:
Sub vlookup_ex()
Dim rng As Range
Dim product_name As String
product_name = "Rice"
Set rng = Range("B2:D10")
rng.Cells(Application.VLookup(product_name, rng, 2, False)).EntireRow.Font.ColorIndex = 3
End Sub
Result
How is it done?
- We declared a range object, rng
- After that, we set B2 to D10 in that range.
- This is followed by using the Cell’s property of range object.
- In the cell property, the VLookup function is used with product_name = “Rice”
- As this cell was returned by VLookup, we used EntireRow.Font.ColorIndex property to set the font color of the whole row as shown in the graphic above.
Setting Interior color, Bold and Italic for the whole row
The example below sets:
- Background color
- Bold
- Italic
For the whole row that is returned for product name = Rice
Code:
Sub vlookup_ex()
Dim rng As Range
Dim product_name As String
product_name = "Rice"
Set rng = Range("B2:D10")
rng.Cells(Application.VLookup(product_name, rng, 2, False)).EntireRow.Interior.ColorIndex = 7
rng.Cells(Application.VLookup(product_name, rng, 2, False)).EntireRow.Font.Bold = True
rng.Cells(Application.VLookup(product_name, rng, 2, False)).EntireRow.Font.Italic = True
End Sub
Result:
Taking User input for terms to be searched
In this example, we will take user input for entering the product name by using InputBox.
Then VLookup function will search for the product price.
Price will be displayed in the message box:
Sub vlookup_ex()
Dim rng As Range
Dim product_name As String
Set rng = Range("B2:D10")
'Taking User Input for Product Name
product_name = InputBox("Enter an Existing Product Name: ")
Price = Application.VLookup(product_name, rng, 2, False)
MsgBox "Price of " & product_name & " = " & Price
End Sub
As we entered “Plates”:
Error handling for wrong search term
So, if the given term (product name in our case) is wrong, or does not exist in the given column then an error will be generated. For example, as entered “Corn”, the program generated the following error:
You may show a descriptive message rather than allow crashing the program by handling the error.
We dealt with error 13 below:
Sub vlookup_ex()
On Error GoTo VlookError:
Dim rng As Range
Dim product_name As String
Set rng = Range("B2:D10")
'Taking User Input for Product Name
product_name = InputBox("Enter an Existing Product Name: ")
Price = Application.VLookup(product_name, rng, 2, False)
MsgBox "Price of " & product_name & " = " & Price
VlookError:
If Err.Number = 13 Then
MsgBox product_name & " does not exist. Enter a Valid Product Name!"
End If
End Sub
Sample output as we entered “Corn” now:
