Finding exact matches from a 2D array I have this code that finds exact matches from a 2D array in a range of two columns on a sheet.

It works ok until the data is formatted as a table then it is slow, really slow. There must be a better way to do this.

Sub compareManifest()
Dim manifest As Worksheet: Set manifest = ActiveWorkbook.Worksheets("Manifest")
Dim approved As Worksheet: Set approved = ActiveWorkbook.Worksheets("Approved Master")
Dim result As Range
Dim findInRange As Range
Dim softwareArray As Variant
' 2d array of software name and version
softwareArray = manifest.Range("A2:B" &amp; lastRow(manifest))
' one of several ranges of software names and versions
Set findInRange = approved.Range("E1:E" &amp; lastRow(approved))
For i = LBound(softwareArray) To UBound(softwareArray)
    Set result = findSoftware(approved, softwareArray(i, 1), softwareArray(i, 2), findInRange)
    If Not result Is Nothing Then
        ' write back some data from successful matches which is the whole point of this exercise
        manifest.Cells(i + 1, 3) = result.Offset(0, -2)
    End If
Next i
End Sub

Function findSoftware(sheet, softwareName, softwareVersion, findInRange) As Range
Dim found As Range
Dim firstFoundAddress As String
Set found = findInRange.Find(what:=softwareName, LookIn:=xlValues, lookat:=xlWhole)
If Not found Is Nothing Then
    firstFoundAddress = found.Address
    Do
        If softwareVersion = sheet.Range(found.Address).Offset(0, 1) Then
            Set findSoftware = sheet.Range(found.Address)
            firstFoundAddress = found.Address
        Else
            Set found = findInRange.FindNext(found)
        End If
    Loop While firstFoundAddress &lt;&gt; found.Address
End If
End Function
Consider using a Collection when matching values in multiple list.  Scripting Dictionaries are well suited for this task.  You'll need to create a Key by concatenate the multiple values with a delimiter.  You will then store the Key with its associated Cell reference  in the Dictionary.

This solution can be further optimized using array but should process 2 list with 10k entries each in seconds (less than a second if optimised using Arrays).

Sub CompareLists()
    Dim cell As Object, dic As Object, key As String
    Set dic = CreateObject("Scripting.Dictionary")

    With ActiveWorkbook.Worksheets("Approved Master")
        For Each cell In .Range("E1" &amp; .Range("E" &amp; .Rows.count).End(xlUp))
            key = cell.Value &amp; "|" &amp; cell.Offset(0, 1).Value
            If Not dic.Exists(key) Then dic.Add key, cell
        Next
    End With

    With ActiveWorkbook.Worksheets("Manifest")
        For Each cell In .Range("B2" &amp; .Range("A" &amp; .Rows.count).End(xlUp))
            key = cell.Value &amp; "|" &amp; cell.Offset(0, 1).Value
            If dic.Exists(key) Then dic.Add key, cell.Offset(0, 2).Value = dic(key).Offset(0, -2).Value
        Next
    End With

End Sub


Here is a good reference to learn how to use Scripting Dictionaries: Excel VBA Introduction Part 39 - Dictionaries.