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, useIsError(result)to check. - With
WorksheetFunction.VLOOKUP, useOn Error Resume Nextbefore 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.IndexandApplication.Match. - XLOOKUP – modern replacement, but only in Excel 2021 or Microsoft 365. In VBA, you can call
Application.XLOOKUPif available. - Find method –
Range.Findcan 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(...)orThisWorkbook.Sheets("Data").Range(...), not justRange(...)which refers to the active sheet. - Forgetting the
Falseargument – 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_lookupas text – useTrueorFalse, 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 = Falseat the start, and set it back toTrueat the end.Application.Calculation = xlCalculationManualwhile running, then restore toxlCalculationAutomatic.- 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
- Excel Template for Personal Expense Tracking
- Excel Template for Project Budget: How to Build One
- Excel Home Budget Template: How to Build One
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA UserForm Data Entry: Build a Simple FormTo enter data through an Excel VBA UserForm, create a form with text boxes and a Save button,…
- Macro Recorder GitHub: How to Find the Right ProjectYes—GitHub has macro-recording projects, but the right choice depends on your operating system and what you want to…
- Excel Macro to Clear Cell Contents: VBA Steps and SafetyTo clear cell contents with an Excel macro, use VBA’s ClearContents method on the cells you want to…
- VBA Open Excel File From Path: Code and Common FixesTo open an Excel workbook from a known path in VBA, call Workbooks.Open and pass the full path…
- Excel VBA Array Formula: How to Enter and Manage ItIn Excel VBA, use Range.FormulaArray to enter a traditional, Ctrl+Shift+Enter array formula, or use Range.Formula2 to enter a…
- Excel VBA: Open a Workbook in the Background, HiddenYes. In Excel VBA, open the file with Workbooks.Open, then set each window in the returned workbook’s Windows…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.