Vlookup Question
 

Hi,

Try this (on test data!).

HTH

Sub matchABC()

Dim mArr() As String
Dim ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet
Dim lastrow As Long, r As Long
Dim outrng As Range
Dim FindMatch As String

Set ws1 = Worksheets("Sheet1")
Set ws2 = Worksheets("Sheet2")
Set ws3 = Worksheets("Sheet3")

Set outrng = ws3.Range("a2")

ws2.Activate
With ws2
lastrow = .Cells(Rows.Count, "B").End(xlUp).Row
ReDim mArr(1 To lastrow - 1)
For r = 2 To lastrow ' Store part number from sheet2
mArr(r - 1) = .Cells(r, 2)
Next r

End With

ws1.Activate
With ws1
lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
For r = 2 To lastrow
FindMatch = .Cells(r, 1)
res = Application.Match(FindMatch, mArr, 0) ' Look for match in
sheet2 list
If Not IsError(res) Then
.Cells(r, 1).Copy outrng ' Part # from sheet1
ws2.Cells(res + 1, 2).Resize(1, 5).Copy outrng.Offset(0, 1) '
Copy B-F to sheet3 from sheet2
Set outrng = outrng.Offset(1, 0)
End If
Next r
End With
End Sub



"DA@PD" wrote:

Okay, I've asked this question before... but I'm still unclear on the solution.

I have a list of part numbers in Column A on Sheet1, I would like to be able
to do an exact match Vlookup with Column B on Sheet 2.

When there is a match, I would like to have the matches generated in column
A of Sheet 3, but in addition to this, if there is a match, I would like the
columns B,C,D,E & F from sheet 2 also populate along with the matching part
number on Sheet 3. When I asked this question before, I was told to just use
Vlookup for the additional data, I have the part numbers only in Sheet 1, no
other columns worth of data to match against Sheet 2. Can anyone Help??

Thanks!