Excel VBA Index Match: How to Use It

VBA & macros · Updated November 22, 2026

The direct answer: in Excel VBA, you use Application.WorksheetFunction.Index and Application.WorksheetFunction.Match together to replicate the INDEX/MATCH formula, for example Application.WorksheetFunction.Index(Sheet2.Range("B2:B100"), Application.WorksheetFunction.Match("Widget", Sheet2.Range("A2:A100"), 0)). This returns the value from column B where column A equals “Widget”.

Why Use INDEX/MATCH in VBA Instead of VLOOKUP

Many Excel users learn VLOOKUP first, but INDEX/MATCH is more flexible. VLOOKUP can only look to the right of the lookup column; INDEX/MATCH can return values from any column, left or right. In VBA, the same limitation applies if you use Application.VLookup. INDEX/MATCH also handles column insertions better—if you insert a column, VLOOKUP’s column index may shift, but INDEX/MATCH references the exact return range. For these reasons, INDEX/MATCH is often the preferred choice for dynamic lookups in VBA.

How INDEX and MATCH Work Together

MATCH finds the position of a value in a range. Its syntax is MATCH(lookup_value, lookup_array, match_type). In VBA, you call it as Application.WorksheetFunction.Match(lookupValue, lookupRange, 0) where 0 means exact match. The result is a number, like 5, meaning the fifth row in the range.

INDEX returns a value from a range at a given row and column. Its syntax is INDEX(array, row_num, [column_num]). In VBA: Application.WorksheetFunction.Index(returnRange, rowNum). When you feed the MATCH result as the row number, INDEX returns the value from the corresponding position in the return range.

Example: Suppose you have product names in A2:A100 and prices in B2:B100. To find the price of “Widget”:

Dim lookupVal As String
Dim rowPos As Long
Dim price As Variant

lookupVal = "Widget"
rowPos = Application.WorksheetFunction.Match(lookupVal, Sheet1.Range("A2:A100"), 0)
price = Application.WorksheetFunction.Index(Sheet1.Range("B2:B100"), rowPos)
MsgBox price

This code stores the position of “Widget” in rowPos, then returns the price from column B at that position. Always save a copy of your workbook before running macros, as VBA actions cannot be undone with Ctrl+Z.

Handling Errors: When MATCH Fails

If MATCH does not find the lookup value, it returns the error #N/A. In VBA, this error is not a normal value—it causes a runtime error if you don’t handle it. You can use On Error statements or check with IsError. A safe pattern is:

Dim rowPos As Variant
rowPos = Application.Match(lookupVal, Sheet1.Range("A2:A100"), 0)
If IsError(rowPos) Then
    MsgBox "Not found"
Else
    price = Application.WorksheetFunction.Index(Sheet1.Range("B2:B100"), rowPos)
End If

Note the use of Application.Match (without WorksheetFunction) which returns a Variant that can hold an error, allowing you to test with IsError. This is a common technique to avoid runtime errors.

Using INDEX/MATCH with Two Criteria

Often you need to look up a value based on two conditions, like product and region. You can do this by using MATCH with concatenated criteria. In a worksheet formula, you’d use an array formula, but in VBA you can use the Evaluate method or create a helper column. A simpler VBA approach is to use Application.Index with a two-dimensional range and two MATCH functions—one for row and one for column.

Example: Data in A1:C10, with headers Product, Region, Sales. To find Sales where Product=”Widget” and Region=”East”:

Dim rowMatch As Variant
Dim colMatch As Variant
Dim result As Variant

rowMatch = Application.Match("Widget", Sheet1.Range("A2:A10"), 0)
colMatch = Application.Match("East", Sheet1.Range("B1:C1"), 0)
If IsError(rowMatch) Or IsError(colMatch) Then
    MsgBox "Not found"
Else
    result = Application.Index(Sheet1.Range("A1:C10"), rowMatch, colMatch + 1) ' +1 to adjust for column A
    MsgBox result
End If

This uses the full data range and matches the column header position. You must adjust the column index because the first column is A, but the header row for regions starts at B.

Performance: INDEX/MATCH vs. Loops

Using INDEX/MATCH in VBA is much faster than looping through cells one by one. For example, a loop that checks each row until it finds a match can be slow for thousands of rows. INDEX/MATCH leverages Excel’s optimized lookup engine. For large datasets, consider reading the entire range into an array and using a dictionary for even faster lookups, but for most tasks INDEX/MATCH is sufficient and easier to write.

Common Mistakes and How to Avoid Them

  • Using WorksheetFunction.Match and not handling errors: As shown above, use Application.Match to avoid runtime errors.
  • Off-by-one errors: MATCH returns a relative position within the lookup range. If your lookup range starts at row 2, MATCH returns 1 for the first data row. INDEX on a range that also starts at row 2 will align correctly. If you use INDEX on a different range, ensure the sizes match.
  • Forgetting to qualify ranges with worksheet: Always include the worksheet, e.g., Sheet1.Range, otherwise VBA may use the active sheet, causing unexpected results.
  • Using exact match vs. approximate: For most lookups you want 0 (exact match). Using 1 or -1 can return approximate matches, which is rarely what you want.

Alternatives: XLOOKUP and VLOOKUP in VBA

In Excel 365 and 2021, you have XLOOKUP, which is simpler and more powerful. In VBA, you can use Application.XLookup if available. It handles errors natively and supports left lookups. Syntax: Application.XLookup(lookup_value, lookup_array, return_array, [if_not_found]). For example:

price = Application.XLookup("Widget", Sheet1.Range("A2:A100"), Sheet1.Range("B2:B100"))

XLOOKUP is not available in older versions, so INDEX/MATCH remains a reliable cross-version choice. VLOOKUP in VBA is still an option but has the right-lookup limitation.

Frequently Asked Questions

How do I use INDEX/MATCH in VBA to return a value from a different sheet? Simply reference the sheet in the range, e.g., Sheet2.Range("B2:B100"). Make sure both lookup and return ranges are on the same sheet or fully qualified.

Can I use INDEX/MATCH with wildcards in VBA? Yes, you can use wildcards like “*” and “?” in the lookup value, but only if the match_type is 0 and the lookup range contains text. For example, Match("Wi*", range, 0) will find the first cell starting with “Wi”.

Why does my VBA code give a type mismatch error? This often happens when MATCH returns an error and you assign it to a Long variable. Use a Variant variable or handle the error first.

Is INDEX/MATCH faster than a For loop? Yes, for most cases. INDEX/MATCH is a compiled C function; a VBA loop interprets each iteration, so INDEX/MATCH is significantly faster on large data.

Can I use INDEX/MATCH to return a whole row or column? Yes, you can use Application.Index with a row number and omit the column argument to return an entire row as an array. But for a single value, you specify both.

What if my data has duplicate lookup values? MATCH returns the first match. If you need the last match, you can use a different approach, like looping from the bottom or using Application.Match on a reversed array, but that’s more advanced.

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.