How to use VLOOKUP in Excel VBA: Step-by-Step (2024)

Visual Basic for Applications (VBA) has many built-in functions that help you work with spreadsheet data.

But “normal” Excel is the undisputed king when it comes to useful functions.

Fortunately, you can use worksheet functions to take advantage of that power when you’re writing macros in VBA.

First, you learn the differences between VBA functions and worksheet functions.

Then I show you how to use a worksheet function in VBA – the VLOOKUP!

Let’s dive in 🤿

Oh, and download the sample workbook here to tag along with this guide.

VBA functions vs. worksheet functions

VBA functions are built into Visual Basic for Applications, the scripting language that you use to create macros.

You can use VBA functions in any program that supports VBA (including Microsoft Word and Access).

Worksheet functions are specific to Excel. They’re the functions that you’re used to using in spreadsheets already—things like SUMIF, IF, and VLOOKUP.

Using worksheet functions saves you time overwriting your own functions in VBA.

To call a worksheet function, you’ll need to use the following syntax:

Application.WorksheetFunction.[function name]

Let’s walk through an example of a very useful function that isn’t present in VBA: the VLOOKUP function.

Using the VLOOKUP function in VBA

We’ll use a simple VLOOKUP formula example to see how worksheet functions can be called in VBA.

VLOOKUP is a very powerful function that’s great for finding information in big spreadsheets.

If you don’t remember, here’s what it does 🧠

VLOOKUP searches for a lookup value in a specified dataset. If it finds it, it returns a corresponding value from the same row.

Unfortunately, it’s not built into VBA. So we’ll have to call it with a worksheet function.

Open the example workbook to follow along. It contains a list of product numbers and descriptions. We’ll use VBA code to look up descriptions based on product numbers.

Here’s the VBA code we’ll use:

Sub findProduct()
 Dim prodNum As Integer, prodDesc As String
 prodNum = Range("F2").Value
 prodDesc = Application.WorksheetFunction.VLookup(prodNum, 
Range("A1:B51"), 2, FALSE)
 MsgBox prodDesc
End Sub

The first two lines of the script are simple; they declare prodNum as an integer variable and prodDesc as a String variable, then assign the value in cell F2 to prodNum.

The VBA VLOOKUP code is actually quite simple.

To use any Excel function in VBA, type “Application.WorksheetFunction.” and start typing the name of the function.

In this case, it’s “VLOOKUP”.

You’ll see it come up in the resulting list (you can also just type the name of the function you’re looking for).

Then you’ll need to include the standard arguments. In the VLOOKUP function, those are:

• lookup_number
• table_array
• col_index_num
• and range_lookup

You enter them similarly to how you would in Excel (if you’ve forgotten how to do that, read my guide here).

But there are a few differences ⚠️

In our case, lookup_number is the variable prodNum, which is similar to selecting a cell in Excel.

The table_array, however, needs to be presented in a format that VBA can handle. Here we’ve used Range(“A1:B51”), which selects the cells in A1:B51.

It’s important to remember that you can’t just type “A1:B51”, as VBA won’t recognize the range ⛔

col_index_num and range_lookup are the same as in Excel. We’re looking in the second column and want an exact match for the product number, so we’ll use 2 and FALSE.

So, use the below code to create a VBA VLOOKUP.

Application.WorksheetFunction.VLookup(prodNum, Range("A1:B51"), 2, FALSE)

Make sure that there’s a product number (the lookup value) in F2:

Then run the macro:

VBA displays a message box with the matching product description for our product number.

Why use worksheet functions in VBA?

This is more complicated than just using the function in Excel, so why would you write an Excel VBA macro for a VLOOKUP function?

Any function that’s present in Excel is going to be slightly more complicated to use in VBA code. But having access to them gives you the option of very powerful automation.

For example, if you wanted to run multiple VLOOKUPs and have the results put in a table, you might find that you need to manually create each VLOOKUP in Excel ⚙️

Or you could write Excel VBA code with the VBA VLOOKUP function that automates the process and saves you time. Even if it is possible to use functions in Excel, it will likely be much more efficient in VBA. You can also save the script and run it on other spreadsheets.

Remember that you can use almost any Excel function from within VBA. Just type “Application.WorksheetFunction.” and you’ll see options in the VBA window.

That’s it – Now what?

Well done learning VLOOKUP in VBA. It wasn’t so hard, was it? 🤞

Once you’ve learned to use Excel’s worksheet functions from within Excel VBA, you’re ready to start writing some very powerful VBA code.

Although it’s useful, the VBA VLOOKUP code is just a tiny part of VBA.

If you want to dive deeper into Excel VBA and see some practical examples, enroll in my free 30-minute VBA training course here.

Other resources

If you’re a little rusty on the arguments of the VLOOKUP function: exact or approximate match, the lookup value, the column index number, or the table array – you might want to read up on it here.

Frequently asked questions