Tag Archives: lookup

vba vlookup

VBA VLOOKUP – Using VLOOKUP in VBA

1 Star2 Stars3 Stars4 Stars5 Stars (2 votes, average: 5.00 out of 5)
Loading...

The VLOOKUP function is probably one of the most dreaded functions by beginner Excel users and also one of the most appreciated by the more advanced. VLOOKUP is used to lookup a row in a certain table, based on a value and return a corresponding value in a certain column with that row.

VLOOKUP is easily used in Excel. Today, however, we will explore how to do lookup operations in VBA (VBA VLOOKUP) or how to VLOOKUP in VBA if you prefer.

VBA VLOOKUP WorksheetFunction

First let us explore the simple approach of using the VBA VLOOKUP WorksheetFunction.

For this example table:
vba vlookup table
A simple VLOOKUP operation for say to lookup Roberts age might look like this:
VLOOKUP in Visual Basic equivalent
What about the same in Visual Basic for Applications (VBA)? The below is the equivalent to the VLOOKUP operation in Excel. We can use the WorksheetFunction object to run a VLOOKUP operation as such:

The result:
vba vlookup result

WorksheetFunction Vlookup function

Syntax

The syntax for the Vlookup function in VBA is:

Parameters

Lookup_value
The lookup value – the value which is searched for within the first column of the provided Table_array parameter

Table_array
A VBA Range variable. A table of at least 2 columns or more. The first column is the lookup column.

Col_index_num
The number of the column in the Table_array table VBA Range from which the matching value must be returned.

Range_lookup
An optional parameter. A logical parameter (True or False) that specifies whether you want the VLookup method to find an exact match or an approximate match. True – approximate match, False – exact match.

VBA VLOOKUP Dictionary

Another approach is to use the VBA Dictionary Object. For the same table as above we can use the following code:

What happens above? First we loop through all the rows and create a VBA Dictionary object with a Key-Value pair of all rows. The Dictionary contains only the lookup and matching columns. Next we lookup our desired Key and return the matching Value.

The result:
vba vlookup result
Right. So if both ways provide the same result what is the benefit of building a Dictionary upfront? Well, imagine wanting to lookup multiple values within your VBA code. In the first approach you would have to reintroduce the entire arguments of the Vlookup function. In the second you can run additional simple calls:

The Dictionary approach will in this case prove MUCH FASTER. This is because the Dictionary is built once and then just reused to lookup a certain key as opposed to doing the entire lookup operation on the entire table as a Vlookup operation would do.

If you want to learn more on the most effective way to lookup values in Excel instead, read my article on VLOOKUP vs INDEX MATCH vs SQL vs VBA.