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:

A simple VLOOKUP operation for say **to lookup Roberts age** might look like this:

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:

1 2 |
Debug.Print Application.WorksheetFunction.VLookup( _ "Robert", Worksheets("Sheet1").Range("A1:C5"), 3, False) |

### WorksheetFunction Vlookup function

#### Syntax

The syntax for the Vlookup function in VBA is:

1 |
WorksheetFunctions.Vlookup( Lookup_value, Table_array, Col_index_num, [Range_lookup] ) |

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

1 2 3 4 5 6 7 8 9 |
'Build the Dictionary Dim dict As Object Set dict = CreateObject("Scripting.Dictionary") For Each r In Range("A:A").SpecialCells(xlCellTypeConstants) Call dict.Add(r.Value, r.Offset(0, 2).Value) Next r 'Lookup Debug.Print dict("Robert") |

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:

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:

1 2 3 4 5 6 7 8 9 10 11 |
'Build the Dictionary Dim dict As Object Set dict = CreateObject("Scripting.Dictionary") For Each r In Range("A:A").SpecialCells(xlCellTypeConstants) Call dict.Add(r.Value, r.Offset(0, 2).Value) Next r 'Lookup Debug.Print dict("Robert") Debug.Print dict("Mike") Debug.Print dict("Michael") |

**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.