Vlook-Up using a Macro

[John_Daly]

Hi There,

(Columns A,B,C below)

I need to Vlookup the Column C Value to see if its in Column B. IF it is return the Value in Column A. (This result would be inputted into Column D hopefully)

1234​	Frank@gmail.com	Susan@gmail.com
567474​	michael@gmail.com	Jimmy@gmail.com
47474​	tony@hotmail.com	michael@gmail.com
77464​	john@hotmail.com
737229​	Susan@hotmail.com
84739​	Jimmy@hotmail.com
847483​	Laura@hotmail.com

 

[offthelip]

try this: It uses the dictionary object and variant arrays which makes the code super fast even for large numbers of rows.

Sub DimIndxMat()
   Dim Ary As Variant
   Dim i As Long
   Dim Dic As Object
 
 
   Set Dic = CreateObject("Scripting.dictionary")
   With Worksheets("Sheet1")
      LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
      Ary = .Range(.Cells(1, 1), .Cells(LastRow, 2))
   End With
   For i = 2 To UBound(Ary)
      Dic(Ary(i, 2)) = Ary(i, 1)               ' Load Dictionary with Value in Column A
   Next i
   With Worksheets("Sheet1")
      LastRow = .Cells(Rows.Count, "C").End(xlUp).Row
      inarr = .Range(.Cells(1, 3), .Cells(LastRow, 3))
      outarr = .Range(.Cells(1, 4), .Cells(LastRow, 4))
  
      For i = 2 To LastRow
         If Dic.Exists(inarr(i, 1)) Then  ' check if element exists
         outarr(i, 1) = Dic(inarr(i, 1)) ' Load output array with dictionary value
         Else          ' if it not set to not found
         outarr(i, 1) = "Not Found"
         End If
        
      Next i
      .Range(.Cells(1, 4), .Cells(LastRow, 4)) = outarr  ' write out the output array
    
   End With
End Sub

 

[John_Daly]

Thanks very much and also thanks for considering the large number of rows