# 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: