The VLOOKUP Function in VBA

We will introduce how to use VLOOKUP in VBA with examples.

Use the VLOOKUP() Function in VBA

In Excel, we can use the VLOOKUP() function to look for a value in an array and return its comparable value from another column. The value we want to look at should exist in the first column.

We also have to indicate our requirements, whether we want a perfect match or the one closest to the value. We can access the VLOOKUP by using the worksheet.

Syntax:

# VBA
VLOOKUP(search_value,range,returnIndex_num,[lookupRange])

The search_value means the value we want to search. The range is the range we are working on, returnIndex_num is the column number from where we want the return value and lookupRange denotes whether the match is perfect or close.

There are two possibilities for lookupRange, which can be true or false, or 0 or 1.

Code:

#VBA
Application.WorksheetFunction.vlookup(search_value,range,returnIndex_num,[lookupRange])

Let’s have examples and try to find some data from them. We have data that contains the ID of freelancers, Names, and Projects Submitted.

If we want to search for the projects completed by freelancers having ID 4, we have to find them by using the vlookup() function in VBA.

First, We open the VBA editor by pressing the ALT + F11 key and create a new module from Insert > Module. Next, we create a new sub lookForProj() and assign variables for freelancer_id and projects completed.

We will also set a range in which we want to find the freelancer.

Code:

#VBA
Sub lookForProj()
Dim freelancer_id As Long
Dim projects As Long
freelancer_id = 4
Set newRange = Range("A2:C6")
projects = Application.WorksheetFunction.VLookup(freelancer_id, newRange, 3, False)
MsgBox "Freelancer with ID: " & freelancer_id & " completed " & projects & " projects"
End Sub

Output:

Let’s show another example. Consider data containing the names of products and their sales, and suppose we have added this data in columns B and C.

We want to search for the number of sales by a product by using the vlookup() function. First, let’s create a new module from Insert > Module and create a new sub, lookForSales().

We will assign variables for product_id and sales and set a range in which we want to find the freelancer.

Code:

# VBA
Sub lookForSales()
Dim product_id As Long
Dim sales As Long
product_id = 2
Set newRange = Range("A2:C6")
sales = Application.WorksheetFunction.VLookup(product_id, newRange, 3, False)
MsgBox "Product with ID: " & product_id & " sold " & sales & " times"
End Sub

Output: