The Excel VLOOKUP function allows you to lookup a value in a table based on a corresponding lookup value in the first column of a table. Below we will explore how to use VLOOKUP in VBA using tWorksheetFunctions as well as how to use the VBA Dictionary as an alternate approach.
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.
Excel VLOOKUP Example
We will start with looking at the Excel VLOOKUP function. For this example table:
A simple VLOOKUP operation for say to lookup Roberts age might look like this:
VBA VLOOKUP WorksheetFunction
Now we will explore using the VBA VLOOKUP WorksheetFunction. The below is the equivalent to the VLOOKUP operation in Excel. We can use the WorksheetFunction object to run a VLOOKUP operation as such:
'Get Roberts Age
Debug.Print Application.WorksheetFunction.VLookup("Robert", Worksheets("Sheet1").Range("A1:C5"), 3, False)
'Result: 44
'Get Michael's Surname
Debug.Print Application.WorksheetFunction.VLookup("Michael", Worksheets("Sheet1").Range("A1:C5"), 2, False)
'Result: Cane
WorksheetFunction VLOOKUP function
The syntax for the Vlookup function in VBA is:
WorksheetFunctions.Vlookup( Lookup_value, Table_array, Col_index_num, [Range_lookup] )
VLOOKUP Parameters
| Parameter | Description |
|---|---|
| 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:
'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")
'Result: 44
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.
Benefits of using the VBA Dictionary
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:
'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) 'Add the Age to the dictionary mapped to Names
Next r
'Lookup
Debug.Print dict("Robert") 'Result: 44
Debug.Print dict("Mike") 'Result: 23
Debug.Print dict("Michael") 'Result: 33
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.
