Vlookup Alternative using Dictionary-CodePudding

The code below is the alternative to vlookup.

This lookup then copy values from Column D and E of sheet "Data" to column D and E of sheet "Master" based in the matching values in column A for both worksheets i.e. using 1 criteria only.

Can someone help on how to make the code below to lookup and match 2 criteria i.e. to lookup and match column A and B for both sheets?

Thanks in advance for help...

Option Explicit

Sub VLookup_Alternative()

Dim rng As Range, j As Range, i, lRow As Long, Dict As Object, myArray As Variant

    With Sheets("Data")
        lRow = .Cells(Rows.Count, 1).End(xlUp).Row
        myArray = .Range("A1").Resize(lRow, 4)
        
        Set Dict = CreateObject("scripting.dictionary")
        Dict.CompareMode = vbTextCompare

        For i = 2 To UBound(myArray, 1)
        Dict(myArray(i, 1)) = i
        Next
        
    End With

    With Sheets("Master")
        Set rng = .Range(.Range("A2"), .Range("A" & Rows.Count).End(xlUp))
        
        For Each j In rng
            
            If Dict.exists(j.Value2) Then
                j.Offset(, 3) = myArray(Dict(j.Value2), 3)
                j.Offset(, 4) = myArray(Dict(j.Value2), 4)
            End If
        
        Next j
    
    End With

End Sub

CodePudding user response：

Please, test the next updated version (matching on concatenation of A & B columns. It is a little faster, using an array for comparing the values in the Master sheet based on dictionary items:

Sub VLookup_Alternative_match2Cols()
 Dim shD As Worksheet, shM As Worksheet, rng As Range, j As Range, i As Long
 Dim lRow As Long, Dict As Object, myArray, arrM

   Set shD = Sheets("Data")
   Set shM = Sheets("Master")
    With shD
        lRow = .cells(.rows.count, 1).End(xlUp).row
        myArray = .Range("A1").Resize(lRow, 5).Value2
        
        Set Dict = CreateObject("scripting.dictionary")
        Dict.CompareMode = vbTextCompare
        Debug.Print shD.name
        For i = 2 To UBound(myArray, 1)
            'to return the first occurrence in case of no unique keys:
            If Not Dict.Exists(myArray(i, 1) & myArray(i, 2)) Then
                Dict(myArray(i, 1) & myArray(i, 2)) = i
            End If
        Next
    End With

    With shM
        Set rng = .Range(.Range("A2"), .Range("A" & rows.count).End(xlUp).Offset(0, 4))
        arrM = rng.Value2 'place the range in an array for faster iteration
                          'and processing in memory

        For i = 1 To UBound(arrM)
            If Dict.Exists(arrM(i, 1) & arrM(i, 2)) Then
                arrM(i, 4) = myArray(Dict(arrM(i, 1) & arrM(i, 2)), 4)
                arrM(i, 5) = myArray(Dict(arrM(i, 1) & arrM(i, 2)), 5)
            End If
        Next i
    End With
    rng.value = arrM 'drop the processed array
    MsgBox "Ready..."
End Sub