use this code
the two workbooks must be opened
i put the code Physical Stock.xls in PHYSICAL STOCK sheet
Dim toser, toser_lea, celcount As Long
Dim iruku As Boolean
celcount = 2
iruku = False
toser = Workbooks("Physical Stock").Sheets("PHYSICAL STOCK").Cells(Rows.Count, 1).End(xlUp).Row
toser_lea = Workbooks("Lease Information").Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
For i = 1 To toser
this_val = Workbooks("Physical Stock").Sheets("PHYSICAL STOCK").Cells(i, 1).Value
For ii = 2 To toser_lea
that_val = Workbooks("Lease Information").Sheets("Sheet1").Cells(ii, 1).Value
If this_val = that_val Then
Workbooks("Lease Information").Sheets("Sheet1").Cells(ii, 1).EntireRow.Copy Workbooks("Physical Stock").Sheets("MASTER LIST").Cells(celcount, 1)
celcount = celcount + 1
iruku = True
End If
Next ii
If iruku = False Then
Workbooks("Physical Stock").Sheets("MASTER LIST").Cells(celcount, 1).Value = this_val
Workbooks("Physical Stock").Sheets("MASTER LIST").Cells(celcount, 3).Value = "NOT FOUND"
celcount = celcount + 1
End If
iruku = False
Next i
Application.CutCopyMode = False
Bookmarks