-
1 Attachment(s)
Look up in three columns
Hello everyone
I need to lookup part of string in three columns and retrieve data related to it
I have attached sample workbook ..
In sheets("Main") I need to search for SKU in column C for example : "FNYD024-05XL" >> search for the first part only which is "FNYD024" ..
This is in row 110 in sheets("Table") ... The search would be in three columns B & C & D in sheets("Table")
If found take the values in both columns E & F
If possible I need to do the task using VBA arrays
Thanks advanced for help
-
Try this:-
Code:
Sub Trans()
Dim Ray As Variant, Dic As Object, Sp As Variant, Ac As Integer
Dim Rng As Range, Dn As Range, n As Long
Ray = Sheets("Table").Range("B5").CurrentRegion
Set Dic = CreateObject("scripting.dictionary")
Dic.CompareMode = vbTextCompare
For Ac = 1 To 3
For n = 2 To UBound(Ray, 1)
If Not Ray(n, Ac) = "" Then
Dic(Ray(n, Ac)) = Array(Ray(n, 4), Ray(n, 5))
End If
Next n
Next Ac
With Sheets("Main")
Set Rng = .Range(.Range("C6"), .Range("C" & Rows.Count).End(xlUp))
End With
For Each Dn In Rng
Sp = Split(Dn.Value, "-")
If Dic.exists(Sp(0)) Then
Dn.Offset(, 2).Value = Dic(Sp(0))(0)
Dn.Offset(, 3).Value = Dic(Sp(0))(1)
End If
Next Dn
End Sub
Regrds Mick
-
That's exactly what I was searching for
Thanks a lot for this great and wonderful help
You are very helpful and genius
-
