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
AutoFilteror loop through the concatenated array collecting row numbers. - If your data is in a Table (ListObject): You can use
ListObject.DataBodyRangeto 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
IsErrorbefore 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=– actuallyEvaluatedoes not need it, but it doesn’t hurt. - Performance issues: Avoid using
Evaluateinside 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.Findmight skip hidden cells. UseSpecialCells(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
- Excel Strikethrough: How to Add It & Shortcuts
- Excel Strikethrough: How to Apply & Remove It
- Excel Utility for E-Invoice: What It Does
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel Analysis ToolPak vs ToolPak VBA: Key DifferencesExcel’s Analysis ToolPak is the add-in for running built-in statistical and engineering analyses from the Data tab; Analysis…
- Excel VBA UserForm Option Button GroupsIn an Excel VBA UserForm, put related OptionButton controls inside the same Frame to make them a mutually…
- VBA Find the Last Column in ExcelTo find the last nonempty column in a particular row, start at the worksheet’s far-right column and move…
- VBA Excel Active Workbook Path: Get It CorrectlyIn Excel VBA, use ActiveWorkbook.Path to get the folder path of the workbook that is active; it does…
- Outlook Macros: How to Enable Them Safely in Classic OutlookTo enable VBA macros in classic Outlook for Windows, open File > Options > Trust Center > Trust…
- VBA Code to Open an Excel File: A Complete VBA GuideTo open an existing Excel workbook with VBA, use Workbooks.Open and provide the file’s full path. Paste the…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.