Search from master list

[Jake975]

I have an workbook I’m having an issue with on the first sheet1 I have a list of current names inA, on the second sheet2 I have the master list with search names inA and correct names inC.
Here is my issue:
Sheet1 inA i get a list of names these have unique text in the name.
Sheet2 inA i have the same list without unique identity text but matches part of the name from Sheet1.
i have tried Index & Match and Vlookup but i get mostly right answers and some wrong ones. For example if I put yum.pdf on sheet1 it’s returning a name that it shouldn’t. (Yum isn’t in master list)
i have also tried search but it can’t look in a different sheet.
Basically I want this to happen:
if part of the text in A1 on sheet1 match text from range on Sheet2 return info from same row two columns to the right where text was found. And so on down list on sheet1

 

[mumps]

Try:

Sub CompareNames()
    Application.ScreenUpdating = False
    Dim Rng As Range, WS1 As Worksheet, WS2 As Worksheet, desWS As Worksheet, fnd As Range
    Set WS1 = Sheets("Temp")
    Set WS2 = Sheets("Data List")
    For Each Rng In WS2.Range("C2", WS2.Range("C" & WS2.Rows.Count).End(xlUp))
        Set fnd = WS1.Range("A:A").Find(Rng, LookIn:=xlValues, lookat:=xlPart)
        If Not fnd Is Nothing Then
            fnd.Offset(, 1) = Rng.Offset(, 1)
        End If
    Next Rng
    Application.ScreenUpdating = True
End Sub

 

[Jake975]

thank you that worked like a charm i couldn't figure out how to get it to work. Huge help thanks

 

[mumps]

You are very welcome.