VBA for VLOOKUP: How to Use It in Excel Macros

VBA & macros · Updated November 22, 2026

The direct answer: to run a VLOOKUP in VBA, use Application.VLOOKUP or WorksheetFunction.VLOOKUP inside a macro, for example result = Application.VLOOKUP(lookup_value, range, col_index, False). This lets you automate lookups across sheets, loop through many values, and handle errors that a normal formula can’t easily manage.

Why Use VBA for VLOOKUP Instead of a Formula?

A standard VLOOKUP formula lives in a cell and recalculates when data changes. That’s fine for a one-off lookup, but VBA becomes useful when you need to:

  • Look up hundreds or thousands of values and write results to a report.
  • Run the lookup as part of a larger automation (e.g., after importing data).
  • Handle missing matches without showing #N/A in the sheet.
  • Combine VLOOKUP with loops, conditions, or user prompts.

VBA also runs faster than recalculating thousands of formulas, and it lets you control exactly when and how the lookup happens.

How VLOOKUP Works in VBA: Two Main Methods

There are two ways to call VLOOKUP from VBA, and they behave differently when the value isn’t found.

1. Application.VLOOKUP – returns an error value (like Error 2042) if no match is found. You can test for that error with IsError.

2. WorksheetFunction.VLOOKUP – throws a runtime error if no match is found, so you need On Error Resume Next or On Error GoTo to handle it.

Both use the same arguments as the worksheet function: lookup_value, table_array, col_index_num, and range_lookup (use False for exact match, True or omitted for approximate).

Basic Example: Single VLOOKUP in VBA

Suppose you have a product list in Sheet1 with names in column A and prices in column B (A2:B10). You want to find the price for “Widget” and show it in a message box.

Sub LookupPrice()
    Dim lookupVal As String
    Dim result As Variant
    
    lookupVal = "Widget"
    
    ' Search in A2:B10, return column 2, exact match
    result = Application.VLOOKUP(lookupVal, Sheet1.Range("A2:B10"), 2, False)
    
    If IsError(result) Then
        MsgBox "Not found"
    Else
        MsgBox "Price: " & result
    End If
End Sub

Note that result is declared as Variant because it can hold an error value. If you use WorksheetFunction, the macro stops on a missing match unless you handle the error.

Looping Through Many Lookups

Often you need to look up values from a list in column A and write results to column B. Here’s a macro that does that for rows 2 to 100, assuming the lookup table is on Sheet2 in A2:C500.

Sub FillLookups()
    Dim i As Long
    Dim lookupVal As Variant
    Dim result As Variant
    
    ' Loop through rows 2 to 100 in the active sheet
    For i = 2 To 100
        lookupVal = Cells(i, 1).Value
        If lookupVal <> "" Then
            result = Application.VLOOKUP(lookupVal, Sheet2.Range("A2:C500"), 3, False)
            If IsError(result) Then
                Cells(i, 2).Value = "Not found"
            Else
                Cells(i, 2).Value = result
            End If
        End If
    Next i
End Sub

This writes the third column’s value from the lookup table into column B. Always save a copy of your workbook before running a macro, because macros cannot be undone with Ctrl+Z.

Handling Errors: The #N/A Problem

The most common VLOOKUP issue is #N/A when a value isn’t in the table. In VBA, you can handle this gracefully:

  • With Application.VLOOKUP, use IsError(result) to check.
  • With WorksheetFunction.VLOOKUP, use On Error Resume Next before the call, then check if an error occurred.

Example with WorksheetFunction:

Sub SafeLookup()
    Dim result As Variant
    On Error Resume Next
    result = WorksheetFunction.VLOOKUP("Missing", Sheet1.Range("A2:B10"), 2, False)
    If Err.Number <> 0 Then
        MsgBox "Value not found"
        Err.Clear
    Else
        MsgBox result
    End If
End Sub

Using Application.VLOOKUP is often simpler because it doesn’t interrupt the macro.

VLOOKUP vs. INDEX/MATCH vs. XLOOKUP in VBA

VLOOKUP has limitations: it only looks to the right of the lookup column, and it can’t easily handle multiple criteria. In VBA, you can use other methods:

  • INDEX/MATCH – more flexible, allows left lookups. In VBA, use Application.Index and Application.Match.
  • XLOOKUP – modern replacement, but only in Excel 2021 or Microsoft 365. In VBA, you can call Application.XLOOKUP if available.
  • Find method – Range.Find can locate a value and return the row, which is useful for more complex operations.

Example of INDEX/MATCH in VBA:

Sub IndexMatchExample()
    Dim lookupVal As String
    Dim rowNum As Variant
    Dim result As Variant
    
    lookupVal = "Widget"
    rowNum = Application.Match(lookupVal, Sheet1.Range("A2:A10"), 0)
    
    If IsError(rowNum) Then
        MsgBox "Not found"
    Else
        result = Application.Index(Sheet1.Range("B2:B10"), rowNum)
        MsgBox result
    End If
End Sub

XLOOKUP in VBA works similarly: Application.XLOOKUP(lookupVal, lookup_range, return_range). It’s cleaner and doesn’t require a column index number.

Common Mistakes and How to Avoid Them

  • Using the wrong sheet reference – always qualify ranges like Sheet1.Range(...) or ThisWorkbook.Sheets("Data").Range(...), not just Range(...) which refers to the active sheet.
  • Forgetting the False argument – if you omit it, VLOOKUP uses approximate match, which can return wrong results if your data isn’t sorted.
  • Hard-coding ranges – if your table grows, use dynamic ranges like Range("A2:B" & Cells(Rows.Count, 1).End(xlUp).Row).
  • Not handling errors – always check for errors to avoid runtime crashes.
  • Using Range_lookup as text – use True or False, not “TRUE” or “FALSE”.

Performance Tips for Large Datasets

If you’re looking up thousands of values, VBA can be slow if you write to cells one by one. Speed it up with:

  • Application.ScreenUpdating = False at the start, and set it back to True at the end.
  • Application.Calculation = xlCalculationManual while running, then restore to xlCalculationAutomatic.
  • Read the lookup table into a VBA array once, then loop through the array instead of hitting the worksheet repeatedly.

Example of using an array:

Sub ArrayLookup()
    Dim data As Variant
    Dim i As Long
    Dim lookupVal As String
    Dim found As Boolean
    
    ' Read table into array
    data = Sheet2.Range("A2:B500").Value
    
    lookupVal = "Widget"
    found = False
    
    For i = 1 To UBound(data, 1)
        If data(i, 1) = lookupVal Then
            MsgBox data(i, 2)
            found = True
            Exit For
        End If
    Next i
    
    If Not found Then MsgBox "Not found"
End Sub

This avoids repeated worksheet access and is much faster for large tables.

Frequently Asked Questions

Can I use VLOOKUP in VBA without showing the formula in a cell? Yes. When you use Application.VLOOKUP or WorksheetFunction.VLOOKUP, the lookup happens in memory and you can do anything with the result—show it in a message box, write it to a cell, or use it in further logic. No formula is placed in the sheet.

Why does my VBA VLOOKUP return #N/A even though the value exists? The most common cause is that the lookup value or the table column contains extra spaces, different data types (text vs. number), or the range doesn’t include the correct rows. Check for leading/trailing spaces with Trim, and ensure the lookup column is the first column of the table array.

How do I use VLOOKUP in VBA to search another workbook? You need to reference the workbook and sheet. For example: Application.VLOOKUP(lookupVal, Workbooks("Data.xlsx").Sheets("Sheet1").Range("A2:B10"), 2, False). Make sure the other workbook is open, or use Workbooks.Open first.

What’s the difference between Application.VLOOKUP and WorksheetFunction.VLOOKUP? Application.VLOOKUP returns an error value that you can test with IsError, so it doesn’t stop the macro. WorksheetFunction.VLOOKUP throws a VBA error (runtime error 1004) when the value isn’t found, requiring error handling. Many prefer Application for simplicity.

Can I use VLOOKUP in VBA to return multiple columns? VLOOKUP only returns one column per call. To get multiple columns, either call VLOOKUP multiple times with different column indices, or use INDEX/MATCH with an array formula. Alternatively, use Application.XLOOKUP which can return a range if you use the return_array argument.

Is there a way to do a case-insensitive VLOOKUP in VBA? VLOOKUP is case-insensitive by default, so it doesn’t distinguish between “widget” and “Widget”. If you need case-sensitive matching, use Application.Match with the CompareMethod set to vbBinaryCompare, or use a loop with StrComp.

Related

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.