Look up in three columns

[YasserKhalil]

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

[MickG]

Try this:-

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

[YasserKhalil]

That's exactly what I was searching for
Thanks a lot for this great and wonderful help
You are very helpful and genius

[MickG]

You're very welcome