Excel VBA VLookup: Step-by-Step Guide and 5 Code Examples to Use the VLookup Function in Macros

In this VBA Tutorial, you learn how to use the VLookup function within your macros.

This VBA VLookup 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 clicking the button below.

Use the following Table of Contents to navigate to the section you're interested in.

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:
  • Learn about the Excel VBA Object Model here.
  • Learn about using variables here.
  • Learn about VBA data types here.
  • Learn about working with worksheet functions within VBA here.
• 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

1. 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.
     
     
2. Item: =.
   
   
   • VBA Construct: = operator.
     
     
   • Description: Assigns the value returned by WorksheetFunction.VLookup to the VLookupResult variable.
     
     
3. 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.
     
     
4. 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.
     
     
5. 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.
     
     
6. 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 explicitly declare a variable to represent ColumnIndex, use the Long data type.
     
     

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

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).
• 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/excel-vba-vlookup/

    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

1. 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.
     
     
2. Item: =.
   
   
   • VBA Construct: = operator.
     
     
   • Description: Assigns the value returned by WorksheetFunction.VLookup to the VLookupResult variable.
     
     
3. 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.
     
     
4. 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.
     
     
5. Item: Worksheet.
   
   
   • VBA Construct: Workbook.Worksheets property.
     
     
   • Description: Returns a Worksheet object representing the worksheet that contains TableArray.
     
     
6. 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.
     
     
7. 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 explicitly declare a variable to represent ColumnIndex, use the Long data type.
     
     

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

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).
• 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/excel-vba-vlookup/

    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

1. 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.
     
     
2. Item: =.
   
   
   • VBA Construct: = operator.
     
     
   • Description: Assigns the value returned by WorksheetFunction.VLookup to the VLookupResult variable.
     
     
3. 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.
     
     
4. 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.
     
     
5. Item: Workbook.
   
   
   • VBA Construct: Application.Workbooks property.
     
     
   • Description: Returns a Workbook object representing the workbook that contains Worksheet.
     
     
6. Item: Worksheet.
   
   
   • VBA Construct: Workbook.Worksheets property.
     
     
   • Description: Returns a Worksheet object representing the worksheet (within Workbook) that contains TableArray.
     
     
7. 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.
     
     
8. 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 explicitly declare a variable to represent ColumnIndex, use the Long data type.
     
     

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

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).
• 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/excel-vba-vlookup/

    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 run-time error occurs, control goes to the statement following the statement where the error occurs.
    
    The error-handler in this line #1 is necessary to handle the possible run-time 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.

Line #2: VLookupResult = WorksheetFunction.vlookup(LookupValue, TableArray, ColumnIndex, False)

1. 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.
     
     
2. Item: =.
   
   
   • VBA Construct: = operator.
     
     
   • Description: Assigns the value returned by WorksheetFunction.VLookup to the VLookupResult variable.
     
     
3. 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.
     
     
4. 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.
     
     
5. 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.
     
     
6. 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 explicitly declare a variable to represent ColumnIndex, use the Long data type.
     
     

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

Line #3: If Err.Number = 0 Then

1. 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.
     
     
2. Item: Err.Number.
   
   
   • VBA Construct: Err object and Number property.
     
     
   • Description: The Err object holds information about run-time errors. The Number property returns a numeric value that identifies the error that occurred.
     
     
3. 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.

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

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

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.

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.

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).
• 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/excel-vba-vlookup/

    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.

Line #2: VLookupResult = Application.vlookup(LookupValue, TableArray, ColumnIndex, False)

1. Item: VLookupResult.
   
   
   • VBA Construct: Variable of the Variant data type.
     
     
   • Description: Variable to which you assign the value returned by Application.VLookup.
     
     
2. Item: =.
   
   
   • VBA Construct: = operator.
     
     
   • Description: Assigns the value returned by Application.VLookup to the VLookupResult variable.
     
     
3. 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.
     
     
4. 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.
     
     
5. 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.
     
     
6. 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 explicitly declare a variable to represent ColumnIndex, use the Long data type.
     
     

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

Line #3: If IsError(VLookupResult) = False Then

1. 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.
     
     
2. 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.
     
     
3. 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.

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

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

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.

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.

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).
• 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/excel-vba-vlookup/

    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.

Learn more about Excel VBA VLookup

You can get immediate free access to the example Excel workbook that accompanies this Excel VBA VLookup Tutorial by clicking the button below.