How to Use VLOOKUP Function in Excel VBA (4 Examples)

Get FREE Advanced Excel Exercises with Solutions!

In this article, I’ll show you how you can use the VLOOKUP function of Excel in VBA. You’ll learn to use the VLOOKUP function with a user-defined lookup value, with a cell reference as the lookup value, with a range of values as the lookup value, and with a lookup value that doesn’t match the data set.


VBA Code to Use the VLOOKUP Function in Excel VBA (Quick View)

Sub VLOOKUP_with_User_Defined_Lookup_Value()

Book = InputBox("Enter the Name of the Book: ")

Set Rng = Range("B4:D13")

MsgBox Application.WorksheetFunction.VLookup(Book, Rng, 3, False)

End Sub

VBA Code to Use the VLOOKUP Function in Excel VBA

Explanation of the Code:

  • This code creates a Macro called VLOOKUP_with_User_Defined_Lookup_Value.
  • It takes one input from the user, the name of the book.
  • Then it sets the lookup range of the VLOOKUP function to the range B4:D13.
  • Finally, it applies the VLOOKUP function with the book name as the lookup value, range B4:D13 as the lookup range, and column 3 (D4:D13) as the return column.
  • It returns the price of the book, which is displayed in a Message Box.

VLOOKUP Function in Excel VBA: 4 Examples

Here we’ve got a data set with the Book Names, Authors, and Prices of some books from a bookshop called Martin Bookstore.

Data Set to Use the VLOOKUP Function in Excel VBA

Our objective today is to use the VLOOKUP function in Excel VBA with this data set.


1. VLOOKUP Function with a User-Defined Lookup Value in VBA in Excel

You can use the VLOOKUP function in Excel VBA with a User-Defined Lookup value.

The following code asks for a book name from the user and then shows the price of the book.

VBA Code:

Sub VLOOKUP_with_User_Defined_Lookup_Value()

Book = InputBox("Enter the Name of the Book: ")

Set Rng = Range("B4:D13")

MsgBox Application.WorksheetFunction.VLookup(Book, Rng, 3, False)

End Sub

Note: This code creates a Macro called VLOOKUP_with_User_Defined_Lookup_Value.

VBA Code to Use the VLOOKUP Function in Excel VBA

Output:

First, save the file as an Excel Macro-Enabled Workbook.

Saving the Workbook to Use the VLOOKUP Function in Excel VBA

Then run the Macro.

An Input Box will appear asking you the name of the book.

Here I’ve entered Crime and Punishment.

Entering Input to Use the VLOOKUP Function in Excel VBA

Click OK. And you’ll find a Message Box showing you the price of the book Crime and Punishment ($21.00).

Output of Using the VLOOKUP Function in Excel VBA

Things to Remember:

Here, in this code, we’ve set the lookup range of the VLOOKUP function to B4:D13 and the return column to 3 (Price).

You change these according to your needs.

Read More: How to Use VLOOKUP to Return Multiple Columns in Excel


2. VLOOKUP Function with a Cell Reference as the LOOKUP Value in VBA in Excel

You can also use a cell reference as the lookup value of the VLOOKUP function in VBA.

In cell F4 of the worksheet, we put the name of the book “Mother”.

Now we’ll develop a Macro that’ll show the name of the Author in cell G4.

VBA Code:

Sub VLOOKUP_with_Cell_Reference_as_Lookup_Value()

Range("G4").Value = Application.WorksheetFunction.VLookup(Range("F4"), Range("B4:D13"), 2, False)

End Sub

Note: This code creates a Macro called VLOOKUP_with_Cell_Reference_as_Lookup_Value.

VBA Code to Use the VLOOKUP Function in Excel VBA

Output:

First, save the file as an Excel Macro-Enabled Workbook.

Then run the Macro.

You’ll find the name of the author of the book “Mother” (Maxim Gorky), set in cell G4.

Output of Using the VLOOKUP Function in Excel VBA

Things to Remember:

Here, in this code, we’ve set the lookup value of the VLOOKUP function to the cell F4, lookup range to B4:D13 again, but the return column to 2 this time (Author).

You change these according to your needs.


3. VLOOKUP Function with a Range of Lookup Values in VBA in Excel

This time we’ll use the VLOOKUP function with a range of lookup values in VBA in Excel.

In the range F4:F6 of the worksheet, we have an array of the names of some books.

Now we’ll develop a Macro to put their authors’ names in the range G4:G6.

VBA Code:

Sub VLOOKUP_with_a_Range_as_Lookup_Value()

Range("G4:G6").Value = Application.WorksheetFunction.VLookup(Range("F4:F6"), Range("B4:D13"), 2, False)

End Sub

Note: This code creates a Macro called VLOOKUP_with_a_Range_as_Lookup_Value.

VBA Code to Use the VLOOKUP Function in Excel VBA

Output:

First, save the file as an Excel Macro-Enabled Workbook.

Then run the Macro.

You’ll find the names of the authors of all the books of the range F4:F6 in the range G4:G6.

Output to Use the VLOOKUP Function in Excel VBA

Things to Remember:

In this code, we’ve set the lookup value of the VLOOKUP function to the range F4:F6, the lookup range to B4:D13 again, and the return column to 2 (Author).

You change these according to your needs.


4. VLOOKUP Function in VBA in Excel When the Lookup Value doesn’t Match

Finally, I’ll show you what happens if the VLOOKUP function doesn’t find any match of the lookup value in the first column of the given range in VBA.

In case, it doesn’t find any match, it throws the run-time error 1004.

Look at the following VBA code.

VBA Code:

Sub VLOOKUP_when_the_Lookup_Value_doesnot_Match()

Book = "A Farewell to Arms"

MsgBox Application.WorksheetFunction.VLookup(Book, Range("B4:D13"), 2, False)

End Sub

Note: This code creates a Macro called VLOOKUP_when_the_Lookup_Value_doesnot_Match.

Output:

First, save the file as an Excel Macro-Enabled Workbook.

Then run the Macro.

It will search for a book called A Farewell to Arms” in the first column of the data set (B4:B13), which doesn’t exist there.

So it’ll throw the run-time error 1004.

Error Showing to Use the VLOOKUP Function in Excel VBA


Things to Remember

Here we’ve used the Application.WorksheetFunction property of VBA.

Using this property, you can use any built-in function of Excel in VBA, with the proper arguments.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


Conclusion

Using these methods, you can use the VLOOKUP function of Excel in VBA. Do you have any questions? Feel free to ask us.


Related Articles

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Rifat Hassan
Rifat Hassan

Rifat Hassan, BSc, Electrical and Electronic Engineering, Bangladesh University of Engineering and Technology, has worked with the ExcelDemy project for almost 2 years. Within these 2 years, he has written over 250 articles. He has also conducted a few Boot Camp sessions on effective coding, especially Visual Basic for Applications (VBA). Currently, he is working as a Software Developer to develop and deploy additional add-ins to enhance the customers with a more sophisticated experience with Microsoft Office Suits,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo