This works for me

Sub tryme()
Worksheets("Sheet2").Columns("D:D").ClearContents
mytest = Worksheets("Sheet2").Range("A2")
k = 2
With Worksheets("Sheet1")
mylast = .Cells(Cells.Rows.Count, "A").End(xlUp).Row
For j = 1 To mylast
If .Cells(j, 1) = mytest Then
Worksheets("Sheet2").Cells(k, "D") = .Cells(j, "B")
k = k + 1
End If
Next j
End With
End Sub


best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

wrote in message
...
Hi,

I have worksheet with data as follows :


Col A Col B
ABC 123
ABC 234
ABC 345
AC 456
AC 567
AC 678


In Another Sheet I have
Col A
If the user inputs the ABC in cell A2 then
I need in Col D
123
234
345


I was using formula
=INDEX(Sheet1!$B$1:$B$10,SMALL(IF($A$2=Sheet1!$A$1 :$A$10,ROW(Sheet1!
$A
$1:$A$10),""),ROW(1:1)),0)


I am looking for VBA solution.