Excel VBA Index Match Multiple Criteria

VBA & macros · Updated November 22, 2026

Yes, you can use VBA to perform an INDEX/MATCH lookup with multiple criteria by building a helper column or by using an array formula with MATCH(1, (condition1)*(condition2), 0). The most reliable VBA approach is to use the WorksheetFunction.Match method on an array of concatenated criteria, or to use Application.Evaluate to run an array formula directly.

Why Use Index/Match Instead of VLOOKUP

VLOOKUP has two major limitations that INDEX/MATCH solves: it can only look to the right of the lookup column, and it only handles a single criterion. When you need to find a value based on two or more conditions (for example, a product code and a region), VLOOKUP forces you to create a messy concatenated helper column. INDEX/MATCH in VBA gives you a cleaner, more flexible solution that works entirely in code without altering your worksheet layout.

In Excel formulas, the multi-criteria INDEX/MATCH pattern is:

=INDEX(return_range, MATCH(1, (criteria1_range=criteria1)*(criteria2_range=criteria2), 0))

This is an array formula, so in older Excel versions you had to press Ctrl+Shift+Enter. In Excel 365, it works as a dynamic array formula without special entry. In VBA, you can replicate this logic using the Evaluate function or by building an array in memory.

Method 1: Using Evaluate for a Multi-Criteria Lookup

The simplest VBA code uses Application.Evaluate to run the array formula. This is easy to write and read, but it can be slower if you call it in a loop over thousands of rows.

Assume your data is in columns A (Product), B (Region), and C (Sales), with headers in row 1. You want to find the sales for Product “X” in Region “East”.

Sub LookupWithEvaluate()
    Dim result As Variant
    ' Use Evaluate to run the array formula
    result = Application.Evaluate("INDEX(C2:C100, MATCH(1, (A2:A100=\"X\")*(B2:B100=\"East\"), 0))")
    If Not IsError(result) Then
        MsgBox "Sales: " & result
    Else
        MsgBox "Not found"
    End If
End Sub

Note: In VBA strings, double quotes inside the formula must be escaped by doubling them (""). The above code works if you have data in rows 2 to 100. If the lookup fails, Evaluate returns an error value, which you can test with IsError.

Method 2: Using WorksheetFunction.Match with a Concatenated Array

For better performance, especially in loops, you can load the criteria ranges into VBA arrays and use WorksheetFunction.Match on a concatenated array. This avoids repeated evaluation of worksheet formulas.

Here’s a function that returns the matching row number:

Function FindRowMulti(prod As String, reg As String, rngProd As Range, rngReg As Range) As Long
    Dim arrProd As Variant, arrReg As Variant
    Dim i As Long, temp() As String
    
    ' Read ranges into arrays (faster than cell-by-cell)
    arrProd = rngProd.Value
    arrReg = rngReg.Value
    
    ' Build a temporary array of concatenated criteria
    ReDim temp(1 To UBound(arrProd, 1))
    For i = 1 To UBound(arrProd, 1)
        temp(i) = arrProd(i, 1) & "|" & arrReg(i, 1)
    Next i
    
    ' Use Match on the concatenated array
    FindRowMulti = Application.Match(prod & "|" & reg, temp, 0)
End Function

Then you can call it like this:

Sub TestFindRow()
    Dim rowNum As Long
    rowNum = FindRowMulti("X", "East", Range("A2:A100"), Range("B2:B100"))
    If Not IsError(rowNum) Then
        MsgBox "Row: " & rowNum + 1 ' +1 because array starts at 1, sheet row is offset by header
    Else
        MsgBox "Not found"
    End If
End Sub

This method is faster because it reads the ranges once into memory and uses a single Match call on an array, not on worksheet cells.

Method 3: Using Find Method for Multiple Criteria

Another approach is to use the Range.Find method in a loop, but that is generally slower and less reliable for multiple criteria. However, you can combine AutoFilter with Find to narrow down rows. For example, filter by the first criterion, then use Find on the visible cells for the second criterion. This is useful when you need to retrieve multiple matches, not just the first.

Sub FindWithFilter()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1")
    
    ' Clear any existing filter
    ws.AutoFilterMode = False
    
    ' Apply filter on column A for "X"
    ws.Range("A1:C100").AutoFilter Field:=1, Criteria1:="X"
    
    ' Now find "East" in the filtered column B
    Dim rngVis As Range
    Set rngVis = ws.Range("B2:B100").SpecialCells(xlCellTypeVisible)
    Dim cell As Range
    Set cell = rngVis.Find(What:="East", LookAt:=xlWhole)
    
    If Not cell Is Nothing Then
        MsgBox "Sales: " & cell.Offset(0, 1).Value
    Else
        MsgBox "Not found"
    End If
    
    ws.AutoFilterMode = False
End Sub

This method is intuitive but slower for large datasets because filtering recalculates the sheet. Use it when you need to handle multiple matches or when the criteria are dynamic.

Choosing the Right Method

Here are practical guidelines:

  • Single lookup, one-off: Use Evaluate – it’s short and clear.
  • Many lookups in a loop (e.g., processing thousands of rows): Use the in-memory array method (Method 2) – it’s the fastest.
  • Need to find all matching rows, not just the first: Use AutoFilter or loop through the concatenated array collecting row numbers.
  • If your data is in a Table (ListObject): You can use ListObject.DataBodyRange to reference only the data rows, which avoids header issues.

Common Problems and How to Fix Them

Here are typical errors you might encounter:

  • #N/A error: This means no match was found. Always test with IsError before using the result.
  • Type mismatch: If your criteria are numbers, make sure you compare numbers, not strings. For example, use CLng(cell.Value) if needed.
  • Array formula not calculating: If you use Evaluate, ensure the formula string is correct. A common mistake is missing the leading = – actually Evaluate does not need it, but it doesn’t hurt.
  • Performance issues: Avoid using Evaluate inside a loop. Instead, read the ranges into arrays once and then loop through the arrays.
  • Hidden rows or filters: If your worksheet has filters active, Range.Find might skip hidden cells. Use SpecialCells(xlCellTypeVisible) if you need to respect filters.

Alternative: Using XLOOKUP in VBA

If you are using Excel 2021 or Microsoft 365, you can use the XLOOKUP function, which natively supports multiple criteria by concatenating lookup arrays. In VBA, you can call it via WorksheetFunction.XLookup, but note that it requires the same version. For example:

Sub XLookupExample()
    Dim result As Variant
    ' XLOOKUP with concatenated criteria
    result = WorksheetFunction.XLookup("X|East", Range("A2:A100") & "|" & Range("B2:B100"), Range("C2:C100"))
    If Not IsError(result) Then
        MsgBox result
    Else
        MsgBox "Not found"
    End If
End Sub

Note: This VBA code uses the & operator to concatenate arrays, which works in Excel 365. However, WorksheetFunction.XLookup may not be available in all VBA environments; if you get an error, use Application.Evaluate instead with the XLOOKUP formula.

Frequently Asked Questions

Can I use INDEX/MATCH with three or more criteria?
Yes. Just add more conditions in the MATCH array: MATCH(1, (A2:A100=crit1)*(B2:B100=crit2)*(C2:C100=crit3), 0). In VBA, you can extend the concatenation method by adding more columns to the temporary array.

Why does my Evaluate formula return a #VALUE! error?
This usually happens when the ranges in the formula have different sizes. Ensure all criteria ranges have the same number of rows. Also check that you have escaped double quotes correctly in the VBA string.

Is there a way to return multiple matches with INDEX/MATCH?
INDEX/MATCH only returns the first match. To get all matches, use the FILTER function in Excel 365, or in VBA loop through the array and collect row numbers into a collection.

Does this work on Mac Excel?
Yes, the VBA code is the same on Mac, but keyboard shortcuts differ (e.g., Ctrl+Shift+Enter becomes Cmd+Shift+Enter in the worksheet). The VBA methods themselves are identical.

Can I use this on a large dataset without slowing down?
Yes, if you use the in-memory array method. Avoid Evaluate in loops. Reading ranges into arrays and using Match on a concatenated array is much faster than hitting the worksheet each time.

What if my criteria are dates or numbers?
Convert them to strings consistently. For example, use Format(dateValue, "yyyy-mm-dd") to avoid regional date issues. For numbers, use CStr(number) to ensure exact match.

Final Thoughts and Safety Note

Always save a copy of your workbook before running any VBA macro, because macros cannot be undone with Ctrl+Z. Test your code on a small sample first. If you are new to VBA, start with the Evaluate method for simplicity, then move to the array method when you need speed. Remember that VBA is case-insensitive by default, but if you need case-sensitive matching, you can use the Option Compare Binary statement at the top of your module.

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.