VBA Dictionary - for vlookup

[Mallesh23]

Hi Team,

I want to use dictionary in place of vlookup. my data is Range("A1:c11"),
Expected output is in Column G. [Trying to learn and use dictionary]

=VLOOKUP(G3,$A$2:$C$11,3,0)

Sub UseDictionary()
    ' Get the range of values
    Dim rg As Range
    Set rg = shData.Range("A1:C11")
    
    ' Create the dictionary
    Dim dict As Object
    Set dict = CreateObject("Scripting.Dictionary")
    
    ' Fill the dictionary
    Dim cell As Range
    For Each cell In rg
        dict(cell.Value) = cell.Offset(0, 2).Value
    Next
    
    Dim row As Long
    row = 1
'     Perform the Lookups
    For Each cell In rg
        Range("H" & row).Value = dict(cell.Value)
        row = row + 1
    Next

End Sub

Book1
	A	B	C	D	E	F	G	H
1	Name	Country	Scores			Expected Output
2	Sachin	India	15000			Name	Scores
3	Dhoni	India	12000			Sachin	15000
4	Rahul	India	11000			Dhoni	12000
5	Kohli	India	18000			Ponting	16000
6	Sehwag	India	13000			Gilchrist	10000
7	Ponting	Aus	16000			Andre Russel	4000
8	Gilchrist	Aus	10000			Sanath Jaysurya	11500
9	Andre Russel	West Indies	4000			Jos Buttler	10000
10	Sanath Jaysurya	Sri Lanka	11500
11	Jos Buttler	England	10000			=VLOOKUP(G3,$A$2:$C$11,3,0)
12
Sheet1

Thanks
mg

 

[Yongle]

try this

Sub UseDictionary()
' Get the range of values
    Dim rg As Range
    Set rg = shData.Range("A1:C11")
' Create the dictionary
    Dim dict As Object
    Set dict = CreateObject("Scripting.Dictionary")
' Fill the dictionary
    Dim cell As Range
    For Each cell In rg
        dict(cell.Value) = cell.Offset(0, 2).Value
    Next
'Perform the Lookups
    Set rg = shData.Range("G3:G9")
    For Each cell In rg
        cell = dict(cell.Offset(, -1).Value)
    Next
End Sub

 

[Mallesh23]

Hi Yongle,

Superrrrrrb . Again thank you so much for your help .!!?