Excel – How to use VLOOKUP function in VBA code

VLOOKUP searches for a value in the leftmost column of a table, and then returns a value in the same row from a column you specify in the table. Use VLOOKUP instead of HLOOKUP when your comparison values are located in a column to the left of the data you want to find.

This is a method to execute a VLOOKUP within a range in Excel:

Function TestGivingAmount(AssetID As Integer) As Long

Dim AnnualGiving As Long
Dim GivingDetail2 As Range
Set GivingDetail2 = Worksheets("Giving").Range("I17:Z19")

TestGivingAmount = Application.WorksheetFunction.VLookup(AssetID, GivingDetail2, 3, False)

End Function

This is a function, so it could be called using =TestGivingAmount() in a common cell in a sheet, but the core of this code is the:

Application.WorksheetFunction.VLookup(AssetID, GivingDetail2, 3, False)

It use the AssetID variable/parameter (passed by the user) to define what to search, the GivingDetail2 range variable to define where to search for, and the other 2 common vlookup parameters.