In this VBA Tutorial, you learn how to use the VLookup function within your macros.
This VBA Tutorial is accompanied by an Excel workbook containing the data and macros I use in the examples below. You can get immediate free access to this example workbook by subscribing to the Power Spreadsheets Newsletter.
Use the following Table of Contents to navigate to the section you're interested in.
Table of Contents
Related VBA and Macro Tutorials
The following VBA and Macro Tutorials may help you better understand and implement the contents below:
 General VBA constructs and structures:
 Practical VBA applications and macro examples:
 Learn about working with worksheets here.
You can find additional VBA and Macro Tutorials in the Archives.
#1: VLookup
VBA Code to do a VLookup
To do a VLookup and assign the result to a variable, use a statement with the following structure:
VLookupResult = WorksheetFunction.vlookup(LookupValue, TableArray, ColumnIndex, False)
Process Followed by VBA Code
VBA Statement Explanation
 Item: VLookupResult.
 VBA Construct: Variable.
 Description: Variable to which you assign the value returned by WorksheetFunction.VLookup.
If you explicitly declare a variable to represent VLookupResult, ensure that the data type you use can handle the possible values returned by WorksheetFunction.VLookup.
 VBA Construct: Variable.
 Item: =.
 VBA Construct: = operator.
 Description: Assigns the value returned by WorksheetFunction.VLookup to the VLookupResult variable.
 VBA Construct: = operator.
 Item: WorksheetFunction.vlookup.
 VBA Construct: WorksheetFunction.VLookup method.
 Description: Searches for a value in the first column of TableArray and returns a value in the same row but in column number ColumnIndex of TableArray.
 VBA Construct: WorksheetFunction.VLookup method.
 Item: LookupValue.
 VBA Construct: Arg1 (Lookup_value) parameter of WorksheetFunction.VLookup.
 Description: The value WorksheetFunction.VLookup searches in the first column of TableArray.
You can usually work with the wildcard characters question mark (?) and asterisk (*) when specifying a text LookupValue. A question mark (?) represents any single character. An asterisk (*) represents any sequence of characters. If you want to include an actual question mark or asterisk (not as a wildcard) within LookupValue, add a tilde (~) immediately before the question mark (~?) or asterisk (~*).
Always ensure that there are no data type inconsistencies between LookupValue and the data stored in the first column of TableArray.
For these purposes, the main consideration is that, if LookupValue is of a numeric data type, the data stored within the first column of TableArray must not be stored as Text. Further considerations apply in more specific circumstances. For example, when dealing with a LookupValue of the Date data type, you can usually use the CLng type conversion function to convert the date LookupValue to the Long data type.
If you explicitly declare a variable to represent LookupValue, ensure that the data type you use can handle the value you look for.
 VBA Construct: Arg1 (Lookup_value) parameter of WorksheetFunction.VLookup.
 Item: TableArray.
 VBA Construct: Arg2 (Table_array) parameter of WorksheetFunction.VLookup.
 Description: Table (2 or more columns) with data. WorksheetFunction.VLookup searches for LookupValue in the first column of TableArray. The value returned by WorksheetFunction.VLookup is that located in the same row as LookupValue but column number ColumnIndex of TableArray.
If you explicitly declare a variable to represent TableArray, use a Range object variable.
 VBA Construct: Arg2 (Table_array) parameter of WorksheetFunction.VLookup.
 Item: ColumnIndex.
 VBA Construct: Arg3 (Col_index_num) parameter of WorksheetFunction.VLookup.
 Description: Column number within TableArray where WorksheetFunction.VLookup finds the value to return. For example:

 If you set ColumnIndex to 1, WorksheetFunction.VLookup returns a value from the first column of TableArray.
 If you set ColumnIndex to 2, WorksheetFunction.VLookup returns a value from the second column of TableArray.
 …
 If you set ColumnIndex to #, WorksheetFunction.VLookup returns a value from the #th column of TableArray.
 If you set ColumnIndex to 1, WorksheetFunction.VLookup returns a value from the first column of TableArray.
If you explicitly declare a variable to represent ColumnIndex, use the Long data type.

 VBA Construct: Arg3 (Col_index_num) parameter of WorksheetFunction.VLookup.
 Item: False.
 VBA Construct: Arg4 (Range_lookup) parameter of WorksheetFunction.VLookup.
 Description: When set to False, Arg4 (Range_lookup) specifies that WorksheetFunction.VLookup looks only for an exact match of LookupValue in the first column of TableArray.
If WorksheetFunction.VLookup doesn't find an exact match, it returns an error. If there are several exact matches, WorksheetFunction.VLookup returns the first value it finds.
You can set Arg4 (Range_lookup) to True. In such case, VBA looks for an approximate match. If WorksheetFunction.VLookup doesn't find an exact match, it returns the next largest value that is less than LookupValue.
If you set Arg4 (Range_lookup) to True:
 The first column of TableArray must be organized in ascending order.
 You can't generally use the wildcard characters question mark (?) and asterisk (*) when specifying a text LookupValue.
 The first column of TableArray must be organized in ascending order.
 VBA Construct: Arg4 (Range_lookup) parameter of WorksheetFunction.VLookup.
Macro Example
The macro below does a VLookup according to the following conditions:
 LookupValue: “Raymond Allen” (myLookupValue variable).
 TableArray: The range of cells (within the active worksheet) defined by the following rows and columns:
 First row: 6 (myFirstRow variable).
 Last row: 305 (myLastRow variable).
 First column: 2 (myFirstColumn variable).
 Last column: 3 (myLastColumn variable).
 First row: 6 (myFirstRow variable).
 ColumnIndex: 2.
The value returned by WorksheetFunction.VLookup is assigned to the myVLookupResult variable. The macro displays a message box (MsgBox) which, among other information, includes the value held by myVLookupResult.
Sub basicVLookup() 'Source: https://powerspreadsheets.com/ 'For further information: https://powerspreadsheets.com/excelvbavlookup/ Dim myLookupValue As String Dim myFirstColumn As Long Dim myLastColumn As Long Dim myColumnIndex As Long Dim myFirstRow As Long Dim myLastRow As Long Dim myVLookupResult As Long Dim myTableArray As Range myLookupValue = "Raymond Allen" myFirstColumn = 2 myLastColumn = 3 myColumnIndex = 2 myFirstRow = 6 myLastRow = 305 With ActiveSheet Set myTableArray = .Range(.Cells(myFirstRow, myFirstColumn), .Cells(myLastRow, myLastColumn)) End With myVLookupResult = WorksheetFunction.vlookup(myLookupValue, myTableArray, myColumnIndex, False) MsgBox "Sales by " & myLookupValue & " are " & Format(myVLookupResult, "#,##0") End Sub
Effects of Executing Macro Example
The following GIF illustrates the results of executing this macro example. As expected, the macro looks for “Raymond Allen” in the first column (Full Name) and returns the corresponding value in the second column (Sales) of the table with data.
#2: VLookup in Another Worksheet
VBA Code to do a VLookup in Another Worksheet
To do a VLookup in another (specific) worksheet and assign the result to a variable, use a statement with the following structure:
VLookupResult = WorksheetFunction.vlookup(LookupValue, Worksheet.TableArray, ColumnIndex, False)
Process Followed by VBA Code
VBA Statement Explanation
 Item: VLookupResult.
 VBA Construct: Variable.
 Description: Variable to which you assign the value returned by WorksheetFunction.VLookup.
If you explicitly declare a variable to represent VLookupResult, ensure that the data type you use can handle the possible values returned by WorksheetFunction.VLookup.
 VBA Construct: Variable.
 Item: =.
 VBA Construct: = operator.
 Description: Assigns the value returned by WorksheetFunction.VLookup to the VLookupResult variable.
 VBA Construct: = operator.
 Item: WorksheetFunction.vlookup.
 VBA Construct: WorksheetFunction.VLookup method.
 Description: Searches for a value in the first column of TableArray and returns a value in the same row but in column number ColumnIndex of TableArray.
 VBA Construct: WorksheetFunction.VLookup method.
 Item: LookupValue.
 VBA Construct: Arg1 (Lookup_value) parameter of WorksheetFunction.VLookup.
 Description: The value WorksheetFunction.VLookup searches in the first column of TableArray.
You can usually work with the wildcard characters question mark (?) and asterisk (*) when specifying a text LookupValue. A question mark (?) represents any single character. An asterisk (*) represents any sequence of characters. If you want to include an actual question mark or asterisk (not as a wildcard) within LookupValue, add a tilde (~) immediately before the question mark (~?) or asterisk (~*).
Always ensure that there are no data type inconsistencies between LookupValue and the data stored in the first column of TableArray.
For these purposes, the main consideration is that, if LookupValue is of a numeric data type, the data stored within the first column of TableArray must not be stored as Text. Further considerations apply in more specific circumstances. For example, when dealing with a LookupValue of the Date data type, you can usually use the CLng type conversion function to convert the date LookupValue to the Long data type.
If you explicitly declare a variable to represent LookupValue, ensure that the data type you use can handle the value you look for.
 VBA Construct: Arg1 (Lookup_value) parameter of WorksheetFunction.VLookup.
 Item: Worksheet.
 VBA Construct: Workbook.Worksheets property.
 Description: Returns a Worksheet object representing the worksheet that contains TableArray.
 VBA Construct: Workbook.Worksheets property.
 Item: TableArray.
 VBA Construct: Arg2 (Table_array) parameter of WorksheetFunction.VLookup.
 Description: Table (2 or more columns) with data within Worksheet. WorksheetFunction.VLookup searches for LookupValue in the first column of TableArray. The value returned by WorksheetFunction.VLookup is that located in the same row as LookupValue but column number ColumnIndex of TableArray.
If you explicitly declare a variable to represent TableArray, use a Range object variable. In such case, include Worksheet in the reference you use to assign a Range object to the object variable.
 VBA Construct: Arg2 (Table_array) parameter of WorksheetFunction.VLookup.
 Item: ColumnIndex.
 VBA Construct: Arg3 (Col_index_num) parameter of WorksheetFunction.VLookup.
 Description: Column number within TableArray where WorksheetFunction.VLookup finds the value to return. For example:

 If you set ColumnIndex to 1, WorksheetFunction.VLookup returns a value from the first column of TableArray.
 If you set ColumnIndex to 2, WorksheetFunction.VLookup returns a value from the second column of TableArray.
 …
 If you set ColumnIndex to #, WorksheetFunction.VLookup returns a value from the #th column of TableArray.
 If you set ColumnIndex to 1, WorksheetFunction.VLookup returns a value from the first column of TableArray.
If you explicitly declare a variable to represent ColumnIndex, use the Long data type.

 VBA Construct: Arg3 (Col_index_num) parameter of WorksheetFunction.VLookup.
 Item: False.
 VBA Construct: Arg4 (Range_lookup) parameter of WorksheetFunction.VLookup.
 Description: When set to False, Arg4 (Range_lookup) specifies that WorksheetFunction.VLookup looks only for an exact match of LookupValue in the first column of TableArray.
If WorksheetFunction.VLookup doesn't find an exact match, it returns an error. If there are several exact matches, WorksheetFunction.VLookup returns the first value it finds.
You can set Arg4 (Range_lookup) to True. In such case, VBA looks for an approximate match. If WorksheetFunction.VLookup doesn't find an exact match, it returns the next largest value that is less than LookupValue.
If you set Arg4 (Range_lookup) to True:
 The first column of TableArray must be organized in ascending order.
 You can't generally use the wildcard characters question mark (?) and asterisk (*) when specifying a text LookupValue.
 The first column of TableArray must be organized in ascending order.
 VBA Construct: Arg4 (Range_lookup) parameter of WorksheetFunction.VLookup.
Macro Example
The macro below does a VLookup according to the following conditions:
 LookupValue: “Raymond Allen” (myLookupValue variable).
 TableArray: The range of cells (within the worksheet named “VBA VLookup” in the active workbook) defined by the following rows and columns:
 First row: 6 (myFirstRow variable).
 Last row: 305 (myLastRow variable).
 First column: 2 (myFirstColumn variable).
 Last column: 3 (myLastColumn variable).
 First row: 6 (myFirstRow variable).
 ColumnIndex: 2.
The value returned by WorksheetFunction.VLookup is assigned to the myVLookupResult variable. The macro displays a message box (MsgBox) which, among other information, includes the value held by myVLookupResult.
Sub vLookupAnotherWorksheet() 'Source: https://powerspreadsheets.com/ 'For further information: https://powerspreadsheets.com/excelvbavlookup/ Dim myLookupValue As String Dim myFirstColumn As Long Dim myLastColumn As Long Dim myColumnIndex As Long Dim myFirstRow As Long Dim myLastRow As Long Dim myVLookupResult As Long Dim myTableArray As Range myLookupValue = "Raymond Allen" myFirstColumn = 2 myLastColumn = 3 myColumnIndex = 2 myFirstRow = 6 myLastRow = 305 With Worksheets("VBA VLookup") Set myTableArray = .Range(.Cells(myFirstRow, myFirstColumn), .Cells(myLastRow, myLastColumn)) End With myVLookupResult = WorksheetFunction.vlookup(myLookupValue, myTableArray, myColumnIndex, False) MsgBox "Sales by " & myLookupValue & " are " & Format(myVLookupResult, "#,##0") End Sub
Effects of Executing Macro Example
The following GIF illustrates the results of executing this macro example. As expected, the macro looks for “Raymond Allen” in the first column (Full Name) and returns the corresponding value in the second column (Sales) of the table with data. In this example, the table with data is in a worksheet (VBA VLookup) that is different from the active worksheet (Blank Active Sheet).
#3: VLookup in Another Workbook
VBA Code to do a VLookup in Another Workbook
To do a VLookup in another (specific) workbook and assign the result to a variable, use a statement with the following structure:
VLookupResult = WorksheetFunction.vlookup(LookupValue, Workbook.Worksheet.TableArray, ColumnIndex, False)
Process Followed by VBA Code
VBA Statement Explanation
 Item: VLookupResult.
 VBA Construct: Variable.
 Description: Variable to which you assign the value returned by WorksheetFunction.VLookup.
If you explicitly declare a variable to represent VLookupResult, ensure that the data type you use can handle the possible values returned by WorksheetFunction.VLookup.
 VBA Construct: Variable.
 Item: =.
 VBA Construct: = operator.
 Description: Assigns the value returned by WorksheetFunction.VLookup to the VLookupResult variable.
 VBA Construct: = operator.
 Item: WorksheetFunction.vlookup.
 VBA Construct: WorksheetFunction.VLookup method.
 Description: Searches for a value in the first column of TableArray and returns a value in the same row but in column number ColumnIndex of TableArray.
 VBA Construct: WorksheetFunction.VLookup method.
 Item: LookupValue.
 VBA Construct: Arg1 (Lookup_value) parameter of WorksheetFunction.VLookup.
 Description: The value WorksheetFunction.VLookup searches in the first column of TableArray.
You can usually work with the wildcard characters question mark (?) and asterisk (*) when specifying a text LookupValue. A question mark (?) represents any single character. An asterisk (*) represents any sequence of characters. If you want to include an actual question mark or asterisk (not as a wildcard) within LookupValue, add a tilde (~) immediately before the question mark (~?) or asterisk (~*).
Always ensure that there are no data type inconsistencies between LookupValue and the data stored in the first column of TableArray.
For these purposes, the main consideration is that, if LookupValue is of a numeric data type, the data stored within the first column of TableArray must not be stored as Text. Further considerations apply in more specific circumstances. For example, when dealing with a LookupValue of the Date data type, you can usually use the CLng type conversion function to convert the date LookupValue to the Long data type.
If you explicitly declare a variable to represent LookupValue, ensure that the data type you use can handle the value you look for.
 VBA Construct: Arg1 (Lookup_value) parameter of WorksheetFunction.VLookup.
 Item: Workbook.
 VBA Construct: Application.Workbooks property.
 Description: Returns a Workbook object representing the workbook that contains Worksheet.
 VBA Construct: Application.Workbooks property.
 Item: Worksheet.
 VBA Construct: Workbook.Worksheets property.
 Description: Returns a Worksheet object representing the worksheet (within Workbook) that contains TableArray.
 VBA Construct: Workbook.Worksheets property.
 Item: TableArray.
 VBA Construct: Arg2 (Table_array) parameter of WorksheetFunction.VLookup.
 Description: Table (2 or more columns) with data within Worksheet. WorksheetFunction.VLookup searches for LookupValue in the first column of TableArray. The value returned by WorksheetFunction.VLookup is that located in the same row as LookupValue but column number ColumnIndex of TableArray.
If you explicitly declare a variable to represent TableArray, use a Range object variable. In such case, include Workbook and Worksheet in the reference you use to assign a Range object to the object variable.
 VBA Construct: Arg2 (Table_array) parameter of WorksheetFunction.VLookup.
 Item: ColumnIndex.
 VBA Construct: Arg3 (Col_index_num) parameter of WorksheetFunction.VLookup.
 Description: Column number within TableArray where WorksheetFunction.VLookup finds the value to return. For example:

 If you set ColumnIndex to 1, WorksheetFunction.VLookup returns a value from the first column of TableArray.
 If you set ColumnIndex to 2, WorksheetFunction.VLookup returns a value from the second column of TableArray.
 …
 If you set ColumnIndex to #, WorksheetFunction.VLookup returns a value from the #th column of TableArray.
 If you set ColumnIndex to 1, WorksheetFunction.VLookup returns a value from the first column of TableArray.
If you explicitly declare a variable to represent ColumnIndex, use the Long data type.

 VBA Construct: Arg3 (Col_index_num) parameter of WorksheetFunction.VLookup.
 Item: False.
 VBA Construct: Arg4 (Range_lookup) parameter of WorksheetFunction.VLookup.
 Description: When set to False, Arg4 (Range_lookup) specifies that WorksheetFunction.VLookup looks only for an exact match of LookupValue in the first column of TableArray.
If WorksheetFunction.VLookup doesn't find an exact match, it returns an error. If there are several exact matches, WorksheetFunction.VLookup returns the first value it finds.
You can set Arg4 (Range_lookup) to True. In such case, VBA looks for an approximate match. If WorksheetFunction.VLookup doesn't find an exact match, it returns the next largest value that is less than LookupValue.
If you set Arg4 (Range_lookup) to True:
 The first column of TableArray must be organized in ascending order.
 You can't generally use the wildcard characters question mark (?) and asterisk (*) when specifying a text LookupValue.
 The first column of TableArray must be organized in ascending order.
 VBA Construct: Arg4 (Range_lookup) parameter of WorksheetFunction.VLookup.
Macro Example
The macro below does a VLookup according to the following conditions:
 LookupValue: “Raymond Allen” (myLookupValue variable).
 TableArray: The range of cells (within the worksheet named “VBA VLookup” in the workbook named “Excel VBA VLookup”) defined by the following rows and columns:
 First row: 6 (myFirstRow variable).
 Last row: 305 (myLastRow variable).
 First column: 2 (myFirstColumn variable).
 Last column: 3 (myLastColumn variable).
 First row: 6 (myFirstRow variable).
 ColumnIndex: 2.
The value returned by WorksheetFunction.VLookup is assigned to the myVLookupResult variable. The macro displays a message box (MsgBox) which, among other information, includes the value held by myVLookupResult.
Sub vLookupAnotherWorkbook() 'Source: https://powerspreadsheets.com/ 'For further information: https://powerspreadsheets.com/excelvbavlookup/ Dim myLookupValue As String Dim myFirstColumn As Long Dim myLastColumn As Long Dim myColumnIndex As Long Dim myFirstRow As Long Dim myLastRow As Long Dim myVLookupResult As Long Dim myTableArray As Range myLookupValue = "Raymond Allen" myFirstColumn = 2 myLastColumn = 3 myColumnIndex = 2 myFirstRow = 6 myLastRow = 305 With Workbooks("Excel VBA VLookup").Worksheets("VBA VLookup") Set myTableArray = .Range(.Cells(myFirstRow, myFirstColumn), .Cells(myLastRow, myLastColumn)) End With myVLookupResult = WorksheetFunction.vlookup(myLookupValue, myTableArray, myColumnIndex, False) MsgBox "Sales by " & myLookupValue & " are " & Format(myVLookupResult, "#,##0") End Sub
Effects of Executing Macro Example
The following GIF illustrates the results of executing this macro example. As expected, the macro looks for “Raymond Allen” in the first column (Full Name) and returns the corresponding value in the second column (Sales) of the table with data. In this example, that data is in a workbook (Excel VBA VLookup) that is different from the active workbook (Book1).
#4: VLookup Error Handling when Using the Application.WorksheetFunction Property
VBA Code to Handle VLookup Errors when Using the Application.WorksheetFunction Property
To handle VLookup errors when using the Application.WorksheetFunction property, use a macro with the following statement structure:
On Error Resume Next VLookupResult = WorksheetFunction.vlookup(LookupValue, TableArray, ColumnIndex, False) If Err.Number = 0 Then StatementsIfNoError Else StatementsIfError End If
Process Followed by VBA Code
VBA Statement Explanation
Line #1: On Error Resume Next
 Item: On Error Resume Next.
 VBA Construct: On Error Resume Next statement.
 Description: Specifies that, when a runtime error occurs, control goes to the statement following the statement where the error occurs.
The errorhandler in this line #1 is necessary to handle the possible runtime errors that line #2 below (with WorksheetFunction.VLookup) can generate.
When using the On Error Resume Next statement within your macros, consider whether it's appropriate to later disable the error handler explicitly by, for example, using the On Error GoTo 0 statement.
 VBA Construct: On Error Resume Next statement.
Line #2: VLookupResult = WorksheetFunction.vlookup(LookupValue, TableArray, ColumnIndex, False)
 Item: VLookupResult.
 VBA Construct: Variable.
 Description: Variable to which you assign the value returned by WorksheetFunction.VLookup.
If you explicitly declare a variable to represent VLookupResult, ensure that the data type you use can handle the possible values returned by WorksheetFunction.VLookup.
 VBA Construct: Variable.
 Item: =.
 VBA Construct: = operator.
 Description: Assigns the value returned by WorksheetFunction.VLookup to the VLookupResult variable.
 VBA Construct: = operator.
 Item: WorksheetFunction.vlookup.
 VBA Construct: WorksheetFunction.VLookup method.
 Description: Searches for a value in the first column of TableArray and returns a value in the same row but in column number ColumnIndex of TableArray.
 VBA Construct: WorksheetFunction.VLookup method.
 Item: LookupValue.
 VBA Construct: Arg1 (Lookup_value) parameter of WorksheetFunction.VLookup.
 Description: The value WorksheetFunction.VLookup searches in the first column of TableArray.
You can usually work with the wildcard characters question mark (?) and asterisk (*) when specifying a text LookupValue. A question mark (?) represents any single character. An asterisk (*) represents any sequence of characters. If you want to include an actual question mark or asterisk (not as a wildcard) within LookupValue, add a tilde (~) immediately before the question mark (~?) or asterisk (~*).
Always ensure that there are no data type inconsistencies between LookupValue and the data stored in the first column of TableArray.
For these purposes, the main consideration is that, if LookupValue is of a numeric data type, the data stored within the first column of TableArray must not be stored as Text. Further considerations apply in more specific circumstances. For example, when dealing with a LookupValue of the Date data type, you can usually use the CLng type conversion function to convert the date LookupValue to the Long data type.
If you explicitly declare a variable to represent LookupValue, ensure that the data type you use can handle the value you look for.
 VBA Construct: Arg1 (Lookup_value) parameter of WorksheetFunction.VLookup.
 Item: TableArray.
 VBA Construct: Arg2 (Table_array) parameter of WorksheetFunction.VLookup.
 Description: Table (2 or more columns) with data. WorksheetFunction.VLookup searches for LookupValue in the first column of TableArray. The value returned by WorksheetFunction.VLookup is that located in the same row as LookupValue but column number ColumnIndex of TableArray.
If you explicitly declare a variable to represent TableArray, use a Range object variable.
 VBA Construct: Arg2 (Table_array) parameter of WorksheetFunction.VLookup.
 Item: ColumnIndex.
 VBA Construct: Arg3 (Col_index_num) parameter of WorksheetFunction.VLookup.
 Description: Column number within TableArray where WorksheetFunction.VLookup finds the value to return. For example:

 If you set ColumnIndex to 1, WorksheetFunction.VLookup returns a value from the first column of TableArray.
 If you set ColumnIndex to 2, WorksheetFunction.VLookup returns a value from the second column of TableArray.
 …
 If you set ColumnIndex to #, WorksheetFunction.VLookup returns a value from the #th column of TableArray.
 If you set ColumnIndex to 1, WorksheetFunction.VLookup returns a value from the first column of TableArray.
If you explicitly declare a variable to represent ColumnIndex, use the Long data type.

 VBA Construct: Arg3 (Col_index_num) parameter of WorksheetFunction.VLookup.
 Item: False.
 VBA Construct: Arg4 (Range_lookup) parameter of WorksheetFunction.VLookup.
 Description: When set to False, Arg4 (Range_lookup) specifies that WorksheetFunction.VLookup looks only for an exact match of LookupValue in the first column of TableArray.
If WorksheetFunction.VLookup doesn't find an exact match, it returns an error. If there are several exact matches, WorksheetFunction.VLookup returns the first value it finds.
You can set Arg4 (Range_lookup) to True. In such case, VBA looks for an approximate match. If WorksheetFunction.VLookup doesn't find an exact match, it returns the next largest value that is less than LookupValue.
If you set Arg4 (Range_lookup) to True:
 The first column of TableArray must be organized in ascending order.
 You can't generally use the wildcard characters question mark (?) and asterisk (*) when specifying a text LookupValue.
 The first column of TableArray must be organized in ascending order.
 VBA Construct: Arg4 (Range_lookup) parameter of WorksheetFunction.VLookup.
Line #3: If Err.Number = 0 Then
 Item: If … Then.
 VBA Construct: Opening line of If… Then… Else statement.
 Description: Conditionally executes the statements within the If… Then block (line #4 below) if the condition specified by item #3 below is met. If the condition isn't met, the statements following the Else clause (line #6 below) are executed.
 VBA Construct: Opening line of If… Then… Else statement.
 Item: Err.Number.
 VBA Construct: Err object and Number property.
 Description: The Err object holds information about runtime errors. The Number property returns a numeric value that identifies the error that occurred.
 VBA Construct: Err object and Number property.
 Item: Err.Number = 0.
 VBA Construct: Condition of If… Then… Else statement.
 Description: This condition is a numeric expression that evaluates to True or False as follows:
 True: When the Number property of the Err object (Err.Number) returns 0. This is the case when there's no error.
 False: When the Number property of the Err object (Err.Number) returns a number other than 0. This is the case when an error occurs.
 True: When the Number property of the Err object (Err.Number) returns 0. This is the case when there's no error.
 VBA Construct: Condition of If… Then… Else statement.
Line #4: StatementsIfNoError
 Item: StatementsIfNoError.
 VBA Construct: Statements within If… Then… Else statement.
 Description: Statements that VBA executes if the condition tested in the opening line of the If… Then… Else statement (line #3 above) is met and returns True. Therefore, these are 1 or more statements that are executed when there's no error (Err.Number = 0).
 VBA Construct: Statements within If… Then… Else statement.
Line #5: Else
 Item: Else.
 VBA Construct: Else clause of If… Then… Else statement.
 Description: The statement(s) following the Else clause (line #6 below) are executed if the condition tested in the opening line of the If… Then… Else statement (line #3 above) isn't met and returns False (Err.Number isn't 0).
 VBA Construct: Else clause of If… Then… Else statement.
Line #6: StatementsIfError
 Item: StatementsIfError.
 VBA Construct: Else Statements of If… Then… Else statement.
 Description: Statements that VBA executes if the condition tested in the opening line of the If… Then… Else statement (line #3 above) isn't met and returns False (Err.Number isn't 0). Therefore, these are 1 or more statements that are executed when there's an error.
 VBA Construct: Else Statements of If… Then… Else statement.
Line #7: End If
 Item: End If.
 VBA Construct: Closing line of If… Then… Else statement.
 Description: Closes the If… Then… Else statement that opened in line #3 above.
 VBA Construct: Closing line of If… Then… Else statement.
Macro Example
The macro below does a VLookup according to the following conditions:
 LookupValue: “Jorge A. Gomez” (myLookupValue variable).
 TableArray: The range of cells (within the worksheet named “VBA VLookup” in the workbook containing the VBA code) defined by the following rows and columns:
 First row: 6 (myFirstRow variable).
 Last row: 305 (myLastRow variable).
 First column: 2 (myFirstColumn variable).
 Last column: 3 (myLastColumn variable).
 First row: 6 (myFirstRow variable).
 ColumnIndex: 2.
The value returned by WorksheetFunction.VLookup is assigned to the myVLookupResult variable. The macro displays a message box (MsgBox) whose contents depend on whether an error has occurred:
 If no error occurs, the message box displays, among other information, the value held by myVLookupResult.
 If an error occurs, the message box displays a confirmation that it didn't found a match for the LookupValue within the first column of TableArray.
Sub vLookupHandleError() 'Source: https://powerspreadsheets.com/ 'For further information: https://powerspreadsheets.com/excelvbavlookup/ Dim myLookupValue As String Dim myFirstColumn As Long Dim myLastColumn As Long Dim myColumnIndex As Long Dim myFirstRow As Long Dim myLastRow As Long Dim myVLookupResult As Long Dim myTableArray As Range myLookupValue = "Jorge A. Gomez" myFirstColumn = 2 myLastColumn = 3 myColumnIndex = 2 myFirstRow = 6 myLastRow = 305 With ThisWorkbook.Worksheets("VBA VLookup") Set myTableArray = .Range(.Cells(myFirstRow, myFirstColumn), .Cells(myLastRow, myLastColumn)) End With On Error Resume Next myVLookupResult = WorksheetFunction.vlookup(myLookupValue, myTableArray, myColumnIndex, False) If Err.Number = 0 Then MsgBox "Sales by " & myLookupValue & " are " & Format(myVLookupResult, "#,##0") Else MsgBox "The Table doesn't contain sales data for " & myLookupValue End If End Sub
Effects of Executing Macro Example
The following GIF illustrates the results of executing this macro example. As expected, the macro looks for “Jorge A. Gomez” in the first column (Full Name). The macro handles the error caused by the fact that no match for this LookupValue is found in that first column.
#5: VLookup Error Handling when Using the Application Object
VBA Code to Handle VLookup Errors when Using the Application Object
To handle VLookup errors when using the Application object, use a macro with the following statement structure:
Dim VLookupResult As Variant VLookupResult = Application.vlookup(LookupValue, TableArray, ColumnIndex, False) If IsError(VLookupResult) = False Then StatementsIfNoError Else StatementsIfError End If
Process Followed by VBA Code
VBA Statement Explanation
Line #1: Dim VLookupResult As Variant
 Item: Dim myVLookupResult As Variant.
 VBA Construct: Dim statement.
 Description:
 Declares a new variable (VLookupResult) as of the Variant data type.
 VLookupResult is the variable to which you assign the value returned by Application.VLookup in line #2 below.
 Declaring the VLookupResult variable as of the Variant data type allows the variable to hold an error value if Application.VLookup in line #2 below returns an error.
 Declares a new variable (VLookupResult) as of the Variant data type.
 VBA Construct: Dim statement.
Line #2: VLookupResult = Application.vlookup(LookupValue, TableArray, ColumnIndex, False)
 Item: VLookupResult.
 VBA Construct: Variable of the Variant data type.
 Description: Variable to which you assign the value returned by Application.VLookup.
 VBA Construct: Variable of the Variant data type.
 Item: =.
 VBA Construct: = operator.
 Description: Assigns the value returned by Application.VLookup to the VLookupResult variable.
 VBA Construct: = operator.
 Item: Application.vlookup.
 VBA Construct: WorksheetFunction.VLookup method.
 Description: Searches for a value in the first column of TableArray and returns a value in the same row but in column number ColumnIndex of TableArray.
 VBA Construct: WorksheetFunction.VLookup method.
 Item: LookupValue.
 VBA Construct: Arg1 (Lookup_value) parameter of Application.VLookup.
 Description: The value Application.VLookup searches in the first column of TableArray.
You can usually work with the wildcard characters question mark (?) and asterisk (*) when specifying a text LookupValue. A question mark (?) represents any single character. An asterisk (*) represents any sequence of characters. If you want to include an actual question mark or asterisk (not as a wildcard) within LookupValue, add a tilde (~) immediately before the question mark (~?) or asterisk (~*).
Always ensure that there are no data type inconsistencies between LookupValue and the data stored in the first column of TableArray.
For these purposes, the main consideration is that, if LookupValue is of a numeric data type, the data stored within the first column of TableArray must not be stored as Text. Further considerations apply in more specific circumstances. For example, when dealing with a LookupValue of the Date data type, you can usually use the CLng type conversion function to convert the date LookupValue to the Long data type.
If you explicitly declare a variable to represent LookupValue, ensure that the data type you use can handle the value you look for.
 VBA Construct: Arg1 (Lookup_value) parameter of Application.VLookup.
 Item: TableArray.
 VBA Construct: Arg2 (Table_array) parameter of Application.VLookup.
 Description: Table (2 or more columns) with data. Application.VLookup searches for LookupValue in the first column of TableArray. The value returned by Application.VLookup is that located in the same row as LookupValue but column number ColumnIndex of TableArray.
If you explicitly declare a variable to represent TableArray, use a Range object variable.
 VBA Construct: Arg2 (Table_array) parameter of Application.VLookup.
 Item: ColumnIndex.
 VBA Construct: Arg3 (Col_index_num) parameter of Application.VLookup.
 Description: Column number within TableArray where Application.VLookup finds the value to return. For example:

 If you set ColumnIndex to 1, Application.VLookup returns a value from the first column of TableArray.
 If you set ColumnIndex to 2, Application.VLookup returns a value from the second column of TableArray.
 …
 If you set ColumnIndex to #, Application.VLookup returns a value from the #th column of TableArray.
 If you set ColumnIndex to 1, Application.VLookup returns a value from the first column of TableArray.
If you explicitly declare a variable to represent ColumnIndex, use the Long data type.

 VBA Construct: Arg3 (Col_index_num) parameter of Application.VLookup.
 Item: False.
 VBA Construct: Arg4 (Range_lookup) parameter of Application.VLookup.
 Description: When set to False, Arg4 (Range_lookup) specifies that Application.VLookup looks only for an exact match of LookupValue in the first column of TableArray.
If Application.VLookup doesn't find an exact match, it returns an error. If there are several exact matches, Application.VLookup returns the first value it finds.
You can set Arg4 (Range_lookup) to True. In such case, VBA looks for an approximate match. If Application.VLookup doesn't find an exact match, it returns the next largest value that is less than LookupValue.
If you set Arg4 (Range_lookup) to True:
 The first column of TableArray must be organized in ascending order.
 You can't generally use the wildcard characters question mark (?) and asterisk (*) when specifying a text LookupValue.
 The first column of TableArray must be organized in ascending order.
 VBA Construct: Arg4 (Range_lookup) parameter of Application.VLookup.
Line #3: If IsError(VLookupResult) = False Then
 Item: If … Then.
 VBA Construct: Opening line of If… Then… Else statement.
 Description: Conditionally executes the statements within the If… Then block (line #4 below) if the condition specified by item #3 below is met.
 VBA Construct: Opening line of If… Then… Else statement.
 Item: IsError (VLookupResult).
 VBA Construct: IsError Function.
 Description: IsError returns a Boolean (True or False) indicating whether the applicable expression (VLookupResult) in an error value.
VLookupResult holds the value returned by Application.VLookup in line #2 above. If Application.VLookup returns an error, VLookupResult holds that error.
 VBA Construct: IsError Function.
 Item: IsError(VLookupResult) = False.
 VBA Construct: Condition of If… Then… Else statement.
 Description: This condition is a numeric expression that evaluates to True or False as follows:
 True: When IsError returns False. This is the case when Application.VLookup doesn't return an error.
 False: When IsError returns True. This is the case when Application.VLookup returns an error.
 True: When IsError returns False. This is the case when Application.VLookup doesn't return an error.
 VBA Construct: Condition of If… Then… Else statement.
Line #4: StatementsIfNoError
 Item: StatementsIfNoError.
 VBA Construct: Statements within If… Then… Else statement.
 Description: Statements that VBA executes if the condition tested in the opening line of the If… Then… Else statement (line #3 above) is met and returns True. Therefore, these are 1 or more statements that are executed when there's no error (IsError(VLookupResult) = False).
 VBA Construct: Statements within If… Then… Else statement.
Line #5: Else
 Item: Else.
 VBA Construct: Else clause of If… Then… Else statement.
 Description: The statement(s) following the Else clause (line #6 below) are executed if the condition tested in the opening line of the If… Then… Else statement (line #3 above) isn't met and returns False (IsError(VLookupResult) isn't False).
 VBA Construct: Else clause of If… Then… Else statement.
Line #6: StatementsIfError
 Item: StatementsIfError.
 VBA Construct: Else Statements of If… Then… Else statement.
 Description: Statements that VBA executes if the condition tested in the opening line of the If… Then… Else statement (line #3 above) isn't met and returns False (IsError(VLookupResult) isn't False). Therefore, these are 1 or more statements that are executed when there's an error.
 VBA Construct: Else Statements of If… Then… Else statement.
Line #7: End If
 Item: End If.
 VBA Construct: Closing line of If… Then… Else statement.
 Description: Closes the If… Then… Else statement that opened in line #3 above.
 VBA Construct: Closing line of If… Then… Else statement.
Macro Example
The macro below does a VLookup according to the following conditions:
 LookupValue: “Jorge A. Gomez” (myLookupValue variable).
 TableArray: The range of cells (within the worksheet named “VBA VLookup” in the workbook containing the VBA code) defined by the following rows and columns:
 First row: 6 (myFirstRow variable).
 Last row: 305 (myLastRow variable).
 First column: 2 (myFirstColumn variable).
 Last column: 3 (myLastColumn variable).
 First row: 6 (myFirstRow variable).
 ColumnIndex: 2.
The value returned by WorksheetFunction.VLookup is assigned to the myVLookupResult variable. The macro displays a message box (MsgBox), whose contents depend on whether an error has occurred:
 If no error occurs, the message box displays, among other information, the value held by myVLookupResult.
 If an error occurs, the message box displays a confirmation that it didn't found a match for the LookupValue within the first column of TableArray.
Sub vLookupHandleErrorAlternative() 'Source: https://powerspreadsheets.com/ 'For further information: https://powerspreadsheets.com/excelvbavlookup/ Dim myLookupValue As String Dim myFirstColumn As Long Dim myLastColumn As Long Dim myColumnIndex As Long Dim myFirstRow As Long Dim myLastRow As Long Dim myVLookupResult As Variant Dim myTableArray As Range myLookupValue = "Jorge A. Gomez" myFirstColumn = 2 myLastColumn = 3 myColumnIndex = 2 myFirstRow = 6 myLastRow = 305 With ThisWorkbook.Worksheets("VBA VLookup") Set myTableArray = .Range(.Cells(myFirstRow, myFirstColumn), .Cells(myLastRow, myLastColumn)) End With myVLookupResult = Application.vlookup(myLookupValue, myTableArray, myColumnIndex, False) If IsError(myVLookupResult) = False Then MsgBox "Sales by " & myLookupValue & " are " & Format(myVLookupResult, "#,##0") Else MsgBox "The Table doesn't contain sales data for " & myLookupValue End If End Sub
Effects of Executing Macro Example
The following GIF illustrates the results of executing this macro example. As expected, the macro looks for “Jorge A. Gomez” in the first column (Full Name). The macro handles the error caused by the fact that no match for this LookupValue is found in that first column.
References to VBA Constructs Used in this VBA Tutorial
Use the following links to visit the appropriate webpage within the Microsoft Developer Network:
 Identify the workbook and worksheet you work with:
 Identify the cell range containing the table where you carry out the VLookup:
 Do a VLookup:
 Handle errors returned by VLookup:
 On Error statement (On Error Resume Next and On Error GoTo 0).
 If… Then… Else statement.
 Err object.
 Number property.
 IsError function.
 On Error statement (On Error Resume Next and On Error GoTo 0).
 Display a message box and format the value returned by VLookup:
 Work with variables and data types:
 Simplify object references: