Executing Vlookup function Through VBA

In this section I have shown two ways of executing the vlookup function using VBA Code.

• Evaluate method
• Worksheet function

Suppose I have a table in "sheet1" which involves Product name and its quantity in column A and B as shown in image below, then we can get the quantity details using product name with the help of vlookup.

FORMULA: =VLOOKUP (“GLASS”,A:B,2,0) would return 100

Evaluate Method

'\\Evaluate Method

Sub Method_One()

Dim vLookup_Result As Double

Dim Lookup_Value As String

Sheets("Sheet1").Select     '\\Dont forget to select the sheet in which your lookup table lies

Lookup_Value = "Glass"

vLookup_Result = Evaluate("VLookup(""" & Lookup_Value & """,A:B,2,0)")

MsgBox "Result Is : " & vLookup_Result

End Sub

Worksheet function Method

'\\Worksheet Function Method

Sub Method_two()

Dim vLookup_Result As Double
Dim Lookup_Value As String
Sheets("Sheet1").Select     '\\Dont forget to select the sheet in which your lookup table lies

Lookup_Value = "Glass"
vLookup_Result = WorksheetFunction.VLookup(Lookup_Value, Range("A:B"), 2, 0)
MsgBox "Result Is : " & vLookup_Result


End Sub