How to Use the VLOOKUP Function in Excel VBA – 4 Examples

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

 Code Breakdown

• Creates a Macro: VLOOKUP_with_User_Defined_Lookup_Value.
• Takes the name of the book.
• Sets the lookup range of the VLOOKUP function: B4:D13.
• 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.
• Returns the price of the book, which is displayed in a Message Box.

The dataset showcases Book Names, Authors, and Prices.

Example 1 – Using the VLOOKUP Function with a User-Defined Lookup Value in VBA in Excel

The following code is used to find the price of a 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: The code creates a Macro: VLOOKUP_with_User_Defined_Lookup_Value.

Output:

• Save the file as an Excel Macro-Enabled Workbook.

• Run the Macro.

In the Input Box, enter the book name. Here, Crime and Punishment.

• Click OK.
• A Message Box shows the price of the book Crime and Punishment ($21.00).

Things to Remember:

The lookup range of the VLOOKUP function is set to B4:D13 and the return column to 3 (Price).

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

Example 2 – Using the VLOOKUP Function with a Cell Reference as the LOOKUP Value in VBA in Excel

In F4, enter the name of the book “Mother”. Create a Macro to show the name of the Author in 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: The code creates a Macro: VLOOKUP_with_Cell_Reference_as_Lookup_Value.

Output:

• Save the file as an Excel Macro-Enabled Workbook.

• Run the Macro.

You’ll find the name of the author (Maxim Gorky) in G4.

Things to Remember:

The lookup value of the VLOOKUP function is set to F4, and the lookup range to B4:D13. The return column is 2 (Author).

You change these according to your needs.

Example 3 – Using the VLOOKUP Function with a Range of Lookup Values in VBA in Excel

Create a Macro to see the authors’ names in 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: VLOOKUP_with_a_Range_as_Lookup_Value.

Output:

• Save the file as an Excel Macro-Enabled Workbook.

• Run the Macro.

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

Things to Remember:

The lookup value of the VLOOKUP function is F4:F6, the lookup range is B4:D13, and the return column is 2 (Author).

You change these according to your needs.

Example 4 – Using the VLOOKUP Function in VBA When the Lookup Value doesn’t Match

If the VLOOKUP returns the run-time error 1004, use 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: VLOOKUP_when_the_Lookup_Value_doesnot_Match.

Output:

• Save the file as an Excel Macro-Enabled Workbook.

• Run the Macro.

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

The run-time error 1004 is displayed.

Download Practice Workbook

Download the practice workbook.

Related Articles

• How to Use VLOOKUP in VBA in Excel
• Use Excel VBA VLOOKUP to Find Values in Another Worksheet
• How to Use VLOOKUP for Multiple Columns in Excel
• VLOOKUP from Multiple Columns with Only One Return in Excel
• How to Use VLOOKUP Function to Compare Two Lists in Excel
• How to Use the VLOOKUP Ascending Order in Excel
• VLOOKUP with Drop Down List in Excel
• How to Use VLOOKUP for Rows in Excel
• How to Use Column Index Number Effectively in Excel VLOOKUP
• Perform VLOOKUP by Using Column Index Number from Another Sheet
• How to Find Column Index Number in Excel VLOOKUP