JIYIK CN >

We will show you how to use it in VBA through examples VLOOKUP.

VLOOKUP()Using functions in VBA

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

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

grammar:

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

search_valuerepresents the value we are searching for. rangeis the range we are working with, returnIndex_numis the column number we want the value returned, lookupRangeand indicates whether the match is perfect or close.

lookupRangeThere are two possibilities, trueeither falseor 0.1

Code:

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

IDLet's take an example and try to find some data from it. Our data contains , , 姓名and for freelancers 提交的项目.

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

First, we open the VBA Editor by pressing the ALT+ key and then create a new module from . Next, we create a new sub and assign variables to and .F11Insert > ModulelookForProj()freelancer_idprojects completed

We will also set the scope in which we want to find freelancers.

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 take another example. Consider data containing product names and their sales, and suppose we have added this data to columns Band C.

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

We'll assign variables product_idto and salesand set the scope in which we want to find freelancers.

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: