Whenever I've had to do stuff like this, there are always differences
(misspellings, extra spaces) that cause trouble. But this may help you get
started with the bulk of them:

Option Explicit
Sub testme()

Dim ListWks As Worksheet
Dim RubWks As Worksheet
Dim myCell As Range
Dim FoundCell As Range

Set ListWks = Worksheets("sheet1")
Set RubWks = Worksheets("sheet2")

With ListWks
For Each myCell In .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))
With myCell
If Application.CountIf(RubWks.UsedRange, .Value) 1 Then
.Offset(0, 1).Value = "Multiple matches"
Else
Set FoundCell = RubWks.Cells.Find(what:=.Value, _
after:=.Cells(1), lookat:=xlWhole, _
searchorder:=xlByRows, _
searchdirection:=xlNext, _
MatchCase:=False)
If FoundCell Is Nothing Then
.Offset(0, 1).Value = "No match"
Else
.Offset(0, 1).Value = FoundCell.Offset(2, 0).Value
End If
End If
End With
Next myCell
End With
End Sub

Each time you run this macro, it plops something into cell to the right.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Leon Jaeggi wrote:

I have been given a excel sheet of 50+ columns 2000+ rows and all over the
place are the some 2500 names and telephone numbers and lots of rubbish -
fortunately the telephone number that belongs to each name is 2 rows down in
the same column. (On a row with other names and lots of rubbish) If you Look
for X, Y, and H names and numbers below you can see what I mean.

X-name rubbish rubbish H-name
rubbish Y-name rubbish rubbish
X-number rubbish rubbish H-number
rubbish Y-number J-name

I have a separate sheet of 400+ names I want the telephone numbers for. All
of my 400 names and numbers are in the 50x2000 row sheet

How can I search for X-name (out of my list) and put in the next column the
number that relates to it from the 50x2000 sheet (two rows down in the same
column)
So I end up with;

X-name X-number
Y-name Y-number
H-name H-number

Regards
Leon

--

Dave Peterson