VBA (Excel): Find Based on Multiple Search Criteria Without Looping

Question

I have a large data sheet that I want to search in VBA based on 3 sets of criteria. Each row entry can be assumed to be unique. The format of the sheet/data itself cannot be changed due to requirements. (I've seen several posts on related questions but haven't found a working solution for this yet.)

At first I used the classic VBA find method in a loop:

Set foundItem = itemRange.Find(What:=itemName, Lookin:=xlValues, lookat:=xlWhole, SearchOrder:=xlByRows)
If Not foundItem Is Nothing Then
    firstMatchAddr = foundItem.Address
    Do
        ' *Check the other fields in this row for a match and exit if found*
        Set foundItem = itemRange.FindNext(foundItem)
    Loop While foundItem.Address <> firstMatchAddr  And Not foundItem Is Nothing
End If

But because this needs to be called a number of times on large sets of data, the speed of this was no good.

I did some searching and found that I could use the match method with index. So I unsuccessfully tried many variations of that such as:

result = Evaluate("=MATCH(1, (""" & criteria1Name & """=A2:A" & lastRow & ")*(""" & criteria2Name & """=B2:B" & lastRow & ")*(""" & criteria3Name & """=C2:C" & lastRow & "), 0)")

And

result = Application.WorksheetFunction.Index(resultRange, Application.WorksheetFunction.Match((criteria1Name = criteria1Range)*(criteria2Name = criteria2Range)*(criteria3Name = criteria3Range))

And

result = Application.WorksheetFunction.Index(resultRange, Application.WorksheetFunction.Match((criteria1Range=criteria1Name )*(criteria2Range=criteria2Name )*(criteria3Range=criteria3Name ))

Then I tried using AutoFilter to sort:

.Range(.Cells(1,1), .Cells(lastRow, lastCol)).AutoFilter Field:=1, Criteria1:="=" & criteria1Name
.Range(.Cells(1,1), .Cells(lastRow, lastCol)).AutoFilter Field:=2, Criteria1:="=" & criteria2Name
.Range(.Cells(1,1), .Cells(lastRow, lastCol)).AutoFilter Field:=3, Criteria1:="=" & criteria3Name

But because one of the sorting columns contains dates, I had issues getting AutoFilter to work properly.

My question is, how can I search through columns in Excel VBA based on multiple criteria, without looping, returning either the row number or the value in the cell of that row that I am interested in?

VBA (Excel): Find Based on Multiple Search Criteria Without Looping

2 Answers