lookup
 

Absolute brilliant

Thanks

Another thing
Assume
Col A Col B
ABC ABC XYZ
ABC 123 ABC

I want match only Col A details with Col B only exact match.

Is application.worksheetfunction.match is the best OR cells.find.....
is the best ?
or is there any better way to match.

Thanks a lot.

On Mar 12, 5:28*pm, "Bernard Liengme" wrote:
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 MVPhttp://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.- Hide quoted text -

- Show quoted text -